topic Re: How to get the Same Day Last Year with Set Analysis in QlikView

How to get the Same Day Last Year with Set Analysis

joseluis_garcia — Fri, 25 Jan 2013 10:27:06 GMT

I want to compare daily sales with the equivalent day last year. The"Calendar" table contains both fields: DateOrder and SameDayLY.

I've tried countless expressions and the results are always wrong. The actual formula I use is

Amount_SDLY = Sum ({$ <SameDayLY=,DateOrder = {'$(=Date(Max(SameDayLY), "dd/MM/yyyy"))'}>} Amount)

Curiously, Date(Max(SameDayLY), "dd/MM/yyyy"))' is correct for every date.

I leave a sample document with dates in spanish format ('DD/MM/YYYY')

Thanks for your help

Re: How to get the Same Day Last Year with Set Analysis

Gysbert_Wassenaar — Fri, 25 Jan 2013 11:03:56 GMT

Set analysis expression are calculated once for the entire chart, not per row. So what you want is not possible with set analysis expressions.

What you could do is add a line to your load script :

left join

LOAD DateOrder as SameDayLY, Amount as AmountLY Resident Sales;

This effectively adds a field AmountLY to your table than contains the Sales of the previous year. You can add the expression sum(AmountLY) to your chart.

Re: How to get the Same Day Last Year with Set Analysis

sbaldwin — Fri, 25 Jan 2013 11:05:40 GMT

Hi, as you want to display it against the actual date you will struggle with just set analysis and you would need ifs etc which would be messy, try splitting the table into two with current and previous tables then you can have two fields one for the current amount and one for previous, eg attached.

Re: How to get the Same Day Last Year with Set Analysis

joseluis_garcia — Fri, 25 Jan 2013 11:31:20 GMT

Thanks to both

I tried to give a correct answer to a wrong approach

As always, small solutions to big problems

Re: How to get the Same Day Last Year with Set Analysis

christian77 — Fri, 25 Jan 2013 13:38:30 GMT

There is also, addyears(yourdate, -1)