topic Re: Set Analysis for YoY in QlikView

Set Analysis for YoY

Wed, 06 Aug 2014 10:20:57 GMT

Hi Experts,

I have data in following format :

Year	Month	Value
2013	Jul	20
2014	Jul	30
2013	Aug	60
2014	Aug	70

I need to use set analysis to display same table in following format :

Month - Year	Value CY	Value PY
Jul-14	30	20
Aug-14	70	60

How can I achieve the same ?

Thanks,

Sailee

Re: Set Analysis for YoY

Yousef_Amarneh — Wed, 06 Aug 2014 10:26:57 GMT

Create a variable and name it vCurrentYear and set max(Year) and create another one for last year

create 2 expressions

sum({$<Year={'$(vCurrentYear)'}>} Value)

sum({$<Year={'$(vLastYear)'}>} Value)

and set a calculated dimension Month & '-' & Year

Re: Set Analysis for YoY

tresB — Wed, 06 Aug 2014 10:37:43 GMT

For multiple years, set analysis would not give you desired result in chart. Better try in the script like:

Input:

load

Year&Month as Key;

Load * Inline [

Year, Month, Value

2013, Jul, 20

2014, Jul, 30

2013, Aug, 60

2014, Aug, 70

] ;

Left Join

Load

Year-1&Month as Key,

Value as PreYValue

Resident Input;

PFA

Re: Set Analysis for YoY

Wed, 06 Aug 2014 10:40:48 GMT

Thanks Yousef. I tried the above, However I get data as shown below. I want Month-Year to show only Month-Current Year and then the columns will have CY and PY data.

Re: Set Analysis for YoY

Yousef_Amarneh — Wed, 06 Aug 2014 10:54:22 GMT

ok, set only month in the dimension and select the month that you want

Re: Set Analysis for YoY

vadimtsushko — Wed, 06 Aug 2014 14:10:48 GMT

Hi Sailee.

I agree with tresesco you cannot achieve that purely by set analisys but I prefer other solution for such problems.

Look at How to trend Current Sales vs Prev Sales, using date as dimension? I believe requirements there are similiar to your's. Look at discussion there - there are several solutions. My solution there (with a sample application) is in creation of special sort of calendar. Good description of that method is at Calendar with flags making set analysis so very simple

Re: Set Analysis for YoY

Thu, 07 Aug 2014 04:22:49 GMT

Thanks tresesco and Vadim Tsushko did at the script level ..

Just have one doubt .. Does preceding loads impact the performance of dashboard ? I do have ample amount of data in the database.

Re: Set Analysis for YoY

tresB — Thu, 07 Aug 2014 04:26:43 GMT

Yes it does, and does it positively. Preceding load is something that should be used wherever possible rather than resident load.