17 Replies Latest reply: Aug 16, 2016 3:58 AM by Beatriz Manjón

# "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?

@

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

May be use calculated dimension:

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

and then 'Suppress When Value is Missing'

or you can use set analysis to only show last 4 months of data

UPDATE: Missed the TOTAL qualifier before

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Hello Sunny,

Many thanks for your response, very appreciate it.

By means of that formula, I can see only data for August and not the rest of the months ( May, June,July)

Maybe I am missing something but this is the dimension I am writing: If(MesYear >= Max(TOTAL MesYear ) - 3, Month) .

On the other part: In my Expression, I have= Count( Distinct product),  In the case  that I am not able to get the limits to my dimension, Do you have any tips about how Can I get the results only for the last 3 months+current month?

Thanks!

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Have you made a selection in Month or MonthYear field? Try this as expression:

=Count(Distinct {<Month, MesYear>} product)

Add another date related field you might have made a selection on

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

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

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

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)

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

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

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

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?

KR

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

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)

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

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!

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

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

Thanks,

Sunny

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Hello Sunny,

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

NULL handling in QlikView

Besr Regards.

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Try using this as dimension

If(MonthYear >= Max(MonthYear,3), Month)

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Thanks for your response Clever Anjos,

I do not why you expressio do not work in my dimension :

If(MesYear>= Max(MesYear,3), Month)

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

That was an example

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Hello ,

My fields name are "MesYear" and "Month" so the formulas I was using is If(MesYear>= Max(MesYear,3), Month), then in the dimension , I have tried to add a calculated dimension as "If(MesYear>= Max(MesYear,3), Month)", but it doesn't work.

Here you have an example about what I want 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:

 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

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Can you please elaborate more with example data and expected output

• ###### Re: "Month" as Dimension and limit the dimenstion in a pivot table

Hello Anil Babu,

Here you have an example about what I want 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:

 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