topic Re: Pivot table in QlikView

Pivot table

upaliwije — Fri, 18 Apr 2014 09:02:41 GMT

Hi Friends

I have attached document with two pivot tables. The both tables are working fine as they are. But when I select month other than 1 (jan)

last column of the 2nd pivot table shows no value. If i select month 2 last two columns of the 2nd pivot table become zero. Pls help me to solve this problem by modifying the set expression

Re: Pivot table

bbi_mba_76 — Fri, 18 Apr 2014 09:11:53 GMT

Hi,

if you delete the label expression you can see the expression in the header

And you can see that your set analysis is searching for month 13, when I selected 4

Re: Pivot table

SunilChauhan — Fri, 18 Apr 2014 09:18:36 GMT

Months are not connected hence the problem

in first table add one more column

MONTH as MonthId

reload and check

hope this work for you

Re: Pivot table

upaliwije — Fri, 18 Apr 2014 09:22:35 GMT

Hi BBI

Instead of searching 13 when you select month 4 I want to modify the set analysis to search Next month. That is Jan .

Pls correct my Set expression to obtain the desired results

Re: Pivot table

Gysbert_Wassenaar — Fri, 18 Apr 2014 09:39:15 GMT

The problem is that you take the max of the MonthId and add 11. For months larger than 1 you end up with values of 13 and higher, which don't exist in your data.

MonthId: 2 => MONTH={$(=Max(MonthId)+11)} => MONTH={$(=2+11)} => MONTH={13} => no data, sum = 0.

Try adding a mod in the expression:

sum({<Year={$(=Max(YEAR))},MONTH={$(=mod(Max(MonthId)+11,12)+1)}>}$(=Upper(Month(AddMonths(MakeDate($(=Max(YEAR)), $(=max(MonthId))),11)))&'_NRP'))

Re: Pivot table

bbi_mba_76 — Fri, 18 Apr 2014 09:43:33 GMT

I think you have to use autonumber on the period (YYYY*100+MM) in the script.

This way if I select a period (generated ID 14) you can add 11 and retrieve the 11th period consecutive(generated ID 25)

Re: Pivot table

upaliwije — Fri, 18 Apr 2014 09:44:26 GMT

So can you please modify my expression to avoid month 13 and return month 1 when month exceeds 12

Re: Pivot table

upaliwije — Fri, 18 Apr 2014 10:20:35 GMT

Thanks It is working fine