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

1 Solution

Accepted Solutions
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

View solution in original post

17 Replies
sunny_talwar

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

Clever_Anjos
Employee
Employee

Try using this as dimension

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

Anil_Babu_Samineni

Can you please elaborate more with example data and expected output

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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!

Not applicable
Author

Thanks for your response Clever Anjos,

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

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


Thanks in advance!

sunny_talwar

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

Clever_Anjos
Employee
Employee

Which are your fields name?

That was an example

Not applicable
Author

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)

 

StoreProductMonth Jan'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:

 

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

Thanks in advance

Not applicable
Author

Hello Anil Babu,

Thanks for your interest,

Here you have an example about what I want to get:


Pivot table:

Expresion: Count( distinct Product)

 

StoreProductMonth Jan'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:

 

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

Thanks in advance!