"Month" as Dimension and limit the dimenstion in a pivot table

[#1]

Hello all,

I woudl like to find out any tips or solution to my problem:

I am working with a pivot table chart ( there is not a "Dimension limits" part for the pivot tables), and as a Dimension, I have "Month". The thing is that I am only interested in reporting the information of the last 3 months+ Current month ( May+June+July+August in this case).

Could anyone help me with this issue?

@

Thanks in advance

[#2]

Thanks Sunny for your help as always.

Here you have an output example of what I try to get:

Pivot table:

Expresion: Count( distinct Product)

Store	Product	Month	Jan'16	Feb'16	Mar'15	Apr'16	May'16	Jun'16	July'16	Aug'16
A	X		1	0	1	1	1	1	0	1
B	X		1	0	0	1	1	0	0	1
B	Y		1	1	1	1	1	0	0	0

What I would like to show it's the last 3 months+Current Month data as follows:

Store	Product	Month	May'16	Jun'16	July'16	Aug'16
A	X		1	1	0	1
B	X		1	0	0	1
B	Y		1	0	0	0

Thanks in advance

[sunny_talwar · Accepted solution]

I have added an attachment with two possible solutions

1) Calculated Dimension

=If(MonthYear >= AddMonths(Max(TOTAL MonthYear), -3), MonthYear)

2) Set Analysis

=Count(DISTINT {<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -3), 'MMM-YY') & '<=' & Date(AddMonths(Max(MonthYear), 0), 'MMM-YY'))"}>} Product)

[#4]

Hello Sunny,

Thank you so much for your help AS ALWAYS.

It totally works! And now I know how to do it both by limit dimensions and expressions.

Thanks!!!

BR

[#5]

Hello Sunny,

I have another question regarding this issue.

Taking into accoun the example you provide me, image that the store "B" doesn't have data at all in August'16, so product "X" =0 and "Y"=0. When seeing the pivot table without any selection, it appears the month "August'16" in the pivit table for this store, but when I select the store "B" this month disappears as is has not data at all.

This happens to me in my document , this could have a solution ? or it is completely normal since I do not have any data in August for this store?

Thanks in advance!

KR

[sunny_talwar]

Hi Beatriz -

I have added a option 3 in there with a addition to the expression from option 2. Can you check if this option make any difference. Although to tell you the truth, that's how QlikView works and it is very difficult to program it to show 0 for missing dimensions.

=Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -3), 'MMM-YY') & '<=' & Date(AddMonths(Max(MonthYear), 0), 'MMM-YY'))"}>}Value) +

Avg({1<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -3), 'MMM-YY') & '<=' & Date(AddMonths(Max(MonthYear), 0), 'MMM-YY'))"}, Store = p(Store)>}0)

[#7]

Hello Sunny,

Thanks for ur reply, the example you posted is not what I was refering since product"x" in store "B" in August =1. What I meant was both products=0 in the Store "B" in August'16. Then you can see these missing data in the table, but when you select the store"B" , the month August dissapears. Anyway, I have just seen an article that confirms this, one you do a selection, these missing data do not appear

Thanks for ur support!

[sunny_talwar]

No problem, do you mind providing the link for the document you have read for future reference of other readers

Thanks,

Sunny

[#9]

Hello Sunny,

I founded some explanations in the articles attached in the following thread:

NULL handling in QlikView

Besr Regards.