topic YTD based on selection in QlikView

YTD based on selection

Thu, 27 Nov 2014 15:49:19 GMT

Dear Qlikers,

I am in trouble with a Set Analysis expression.

Suppose to have a Qlik app which include some orders with date and amount (very typical situation).

Then, I have a set analysis expression to calculate YTD amount for this year (i.e. 2014) and for the previous year (2013).

I would like to calculate YTD amount and compare with YTD previous year if you select the current year, which is not complete (for example, 2014/2013), but to calculate the entire amount if you select a previous year (2013/2012)).

YTD amount for 1st year: Sum({$<Year = {"$(=max(Year))"}>} Amount)

If there is no selection it takes 2014 and there is no problem.

If the user select an year (for example, 2013) there is no problem because it takes the all year.

YTD amount for the 2nd year:

Sum({$<OrderDate = {“<=$(AddYears(=Max(OrderDate)),-1)”}, Year = {"$(=max(Year)-1)"}>} Amount)

(Suppose that today is: 27/11/2014)

If there is no selection it takes all the order with order date <= 27/11/2013 and that's ok!

If the user select 2013 and the max order date is 29/12/2013, the expression calculate the YTD previous year date -> 29/12/2012.

But, if there is an order of 31/12/2012 it will not be included!

Where am I wrong?!?

Thanks!

Ps. I know there could be some syntax error in the expression, I wrote directly here to simplify the real situation.

Re: YTD based on selection

rubenmarin — Thu, 27 Nov 2014 16:29:15 GMT

Hi tomasso “<=$(AddYears(=Max(OrderDate)),-1)” will return the max order date minus one year, you can use YearEnd() if you want the full year:

“<=$(=YearEnd(AddYears(Max(OrderDate)),-1))”

Re: YTD based on selection

Thu, 27 Nov 2014 16:46:37 GMT

Thanks for your answer Ruben!

But it's not what I'm looking for.

If I consider this year (2014) I would like to compare data from 1/1/2014 to 27/11/2014 (in the 1st expression) with data from 1/1/2013 to 27/11/2013 (in the 2nd expression). While the expression you suggest will consider the entire year, from 1/1/2013 to 31/12/2013.

Re: YTD based on selection

rubenmarin — Fri, 28 Nov 2014 08:01:44 GMT

OK, so, if you want different max date depending if it's the current or previous years you can use:

“<=$(=If(Max(Year)=Year(Today()), AddYears(Today(),-1), YearEnd(AddYears(Max(OrderDate),-1)))”

Note that I changed max(Order date) in currrent year, ie, if today is 28/11/2014 and the max Order date is 26/11/2014, it will still get amount from 01/01/2013 to 28/11/2013.

If for current year you want to compare to yesterday (not counting today), you can use:

“<=$(=If(Max(Year)=Year(Today()), AddYears(Today()-1,-1), YearEnd(AddYears(Max(OrderDate),-1)))”

Re: YTD based on selection

Fri, 28 Nov 2014 14:45:14 GMT

Thanks Ruben! It works!!!

Could be possible to do it also without using IF?

Re: YTD based on selection

rubenmarin — Mon, 01 Dec 2014 07:49:05 GMT

I don't get how doing it without "if", anyway, set analisys is calculated for the whole table, not row by row, so I don't think that "if" would be a performance issue.