topic Re: Qliksense - Rolling 13 Months Pivot Table in App Development

Qliksense - Rolling 13 Months Pivot Table

praveenak — Fri, 12 May 2017 10:11:44 GMT

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

Re: Qliksense - Rolling 13 Months Pivot Table

sunny_talwar — Fri, 12 May 2017 11:17:12 GMT

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

Re: Qliksense - Rolling 13 Months Pivot Table

praveenak — Fri, 12 May 2017 12:20:28 GMT

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

Re: Qliksense - Rolling 13 Months Pivot Table

sunny_talwar — Fri, 12 May 2017 12:48:44 GMT

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