Skip to main content

# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
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
MVP

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)

17 Replies
MVP

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

Employee

Try using this as dimension

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

Can you please elaborate more with example data and expected output

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!

MVP

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

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)

 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

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)

 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

Thanks in advance!

Community Browser