topic Re: Re: Re: How to write Set analysis to get YTD in QlikView

How to write Set analysis to get YTD

Fri, 30 Aug 2013 15:24:01 GMT

Hi All,

I have the following table:

Table1:

load * Inline

[YearMonth, Revenue

'2013/1/1',100

'2013/2/1',200

'2013/3/1',300

'2013/4/1',400

'2013/5/1',500

];

I want to use one Pivot table to show the YTD revenue, take example:

YearMonth,Revenue

'2013/1/1',100

'2013/2/1',300

'2013/3/1',600

'2013/4/1',1000

'2013/5/1',1500

I use this expression to finish, but it failed...

=sum({<YearMonth={'<=$(=Only(YearMonth))'}>}TOTAL Revenue)

How can I use Set analysis to achieve it?

Thanks.

Re: How to write Set analysis to get YTD

cesaraccardi — Fri, 30 Aug 2013 15:34:47 GMT

Hi Isaac,

I think the issue is with the Date format that you have chosen, it is probably dividing 2013 by 5 then by 1 instead of comparing with the full date "2013/5/1". You could either change the format to DD/MM/YYYY or try to apply a formatting function in your expression, something like:

=sum({<YearMonth={"<=$(=Date#(Only(YearMonth),'YYYY/M/D'))"}>}TOTAL Revenue)

Kind Regards,

Cesar

Re: How to write Set analysis to get YTD

Fri, 30 Aug 2013 15:38:29 GMT

Thanks for your reply, but it does not work, the problem I know is the YearMonth can be captured if we select one option in YearMonth listbox. But if we don't choose any option in YearMonth, the expression does not work.

So the key point is how can we capture the current YearMonth for each row in Pivot table?

Thanks.

Re: How to write Set analysis to get YTD

cesaraccardi — Fri, 30 Aug 2013 15:47:37 GMT

Isaac,

If you add the expression "Only(YearMonth)" you should see the current YearMonth for each row (if you have YearMonth as a dimension or another field in the same level of aggregation). If that's true the issue is related to the comparison <= and the date format.

Are you able to post the application so I can have a look?

Kind Regards,

Cesar

Re: How to write Set analysis to get YTD

abeyphilip — Fri, 30 Aug 2013 15:53:12 GMT

Hi,

If you are fine with using a straight table and no set analysis.

Regards,

Abey

Re: Re: How to write Set analysis to get YTD

Fri, 30 Aug 2013 16:08:36 GMT

It is enclosed...

Re: Re: How to write Set analysis to get YTD

Fri, 30 Aug 2013 16:17:45 GMT

Thanks. but the my question has to be solved by Set analysis.

Re: Re: Re: How to write Set analysis to get YTD

cesaraccardi — Fri, 30 Aug 2013 16:22:33 GMT

Hi Isaac,

I see what you mean now... Please have a look.

Re: Re: How to write Set analysis to get YTD

Fri, 30 Aug 2013 16:31:23 GMT

Yes, it is close to what I wanted. However, the report you provided: the YearMonth listbox does not work, how can the user chooses the YearMonth and also show the right YTD revenue?

Re: Re: How to write Set analysis to get YTD

cesaraccardi — Fri, 30 Aug 2013 16:42:21 GMT

Yes, the way I did will always display all the MonthYear values in the table regardless of selections. What is your requirement? Do you need to show all months or only the latest one in the table?

Re: Re: How to write Set analysis to get YTD

Fri, 30 Aug 2013 16:45:25 GMT

What I want is: If the user just chooses 2013-3-1 and 2013-4-1, the pivot table should show 2 rows: the YTD revenue to 2013-3-1and the YTD revenue to 2013-4-1.

Re: How to write Set analysis to get YTD

kedar_dandekar — Fri, 30 Aug 2013 20:20:12 GMT

Hi Issac,

Considering you want to evaluate your "Expression's - Set Analysis" in the context of the dimension,

a way to achieve it, would be by using an IF clause. Checkout the attached application.

If it meets your requirements, then please do refer to the following article,

it also details how you can form the expression dynamically:

Evaluating "sets" in the context of a dimension

HTH,