topic Re: Set analysis comparison date - 365 in QlikView

Set analysis comparison date - 365

i_villafranca — Tue, 14 Nov 2017 15:58:57 GMT

Dear community,

I am trying to compare the data from this year vs LY. I know it seems pretty simple, but I can´t get it...

My final graph should look like this:

Day	This Year	Last Year
01/01/2018	100	90
02/01/2018	139	175
03/01/2018	147	200
04/01/2018	140	160

I am trying with the following expression, but I don´t get any result:

Sum( {$< Day ={$(=Date(Only(Day)-365,'DD/MM/YYYY'))}>} Revenue)

Thanks for the help!

Re: Set analysis comparison date - 365

marcus_sommer — Sun, 19 Nov 2017 17:14:08 GMT

Try something like this: sum({< Revenue = {"=Day=date(Day-365, 'DD/MM/YYYY)"}>} Revenue)

- Marcus

Re: Set analysis comparison date - 365

Anil_Babu_Samineni — Mon, 20 Nov 2017 03:12:37 GMT

Perhaps this?

Create Field in script like

Year(Day) as Year

And then use this

CY - Sum({<Year = {$(=Year(Today()))}>} Revenue)

LY - Sum({<Year = {$(=Max(Year))>} Revenue)

Re: Set analysis comparison date - 365

antoniotiman — Mon, 20 Nov 2017 06:51:38 GMT

Perhaps You need AsOfTable technique, like this

Table:
LOAD * Inline [
Day,Revenue
01/01/2018,10
02/02/2018,20
03/03/2018,30
04/04/2018,40
01/01/2017,11
02/02/2017,21
03/03/2017,31
04/04/2017,41
];
Temp:
LOAD DISTINCT Day Resident Table;
Join (Temp) LOAD Day as AsOfDay Resident Temp;

LOAD Day,AsOfDay,AsOfDay-Day as DayDiff
Resident Temp
Where AsOfDay >= Day;
Drop Table Temp;

Regards,

Antonio