Qliksense - Rolling 13 Months Pivot Table

[praveenak]

Hi Everyone,

Am working on a pivot table and stuck with below issue.

I have three row-level dimensions (let's say dim1, dim2, dim3),

          one column level dimension (Month field) and

          one measure.

My Data in data model is of 2017, 2016, 2015, 2014.

My Measure expression(to show the percentage value of status='xxx') is like below,

count({<status={'xxx'},Tflag={'1'}>}status) / count({<status=,Tflag={'1'}>}status).

But doing this way, my table will show data for all the years.

Now I'd like to show the data for rolling 13 months, like described in below two lines.

By default on opening the report I need to show data from current month to last 13 months.

And when a month is selected in list box - to show last 13 months from that month selected in filter.

For example, by default on opening the report to show data from May 2017 to April 2016. And If suppose a month(Jan 2017) is selected, pivot table should show data from Jan 2017 to Dec 2015.

Can anyone please help me to solve this?

Thanks

[sunny_talwar]

Assuming you have a MonthYear field created like this

LOAD Date,

    Date(MonthStart(Date), 'MMM-YYYY') as MonthYear

...

You can use this expression

Count({<status={'xxx'},Tflag={'1'}, MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -13), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}>}status)/Count({<status=,Tflag={'1'}>}status)

You can modify the expression based on the format of your MonthYear field or you can use Date field like this (assuming the format to be MM/DD/YYYY)

Count({<status={'xxx'},Tflag={'1'}, Date = {"$(='>=' & Date(MonthStart(Max(Date ), -13), 'MM/DD/YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'MM/DD/YYYY'))"}>}status)/Count({<status=,Tflag={'1'}>}status)

Also, look here:Get the Dates Right

[praveenak]

Hi Sunny,

Thanks for your reply.

But its not working. May be my question is not clear.

What I want to achieve is in the Column level dimension(Month) to show only last 13 months from current month by default on opening the report or from the selected month in list box, rather than it showing all months in the data model for all years.

Looking for your reply again.

Thanks

[sunny_talwar]

Might get easier if you can share a sample with the expected output to help you better