Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

17 Replies
Not applicable
Author

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)

StoreProductMonthJan'16Feb'16Mar'15Apr'16May'16Jun'16July'16Aug'16
AX10111101
BX10011001
BY11111000

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

StoreProductMonthMay'16Jun'16July'16Aug'16
AX1101
BX1001
BY1000

Thanks in advance

sunny_talwar

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)

Capture.PNG

Not applicable
Author

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

Not applicable
Author

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)

Not applicable
Author

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

Not applicable
Author

Hello Sunny,

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

NULL handling in QlikView

Besr Regards.