3 Replies Latest reply: May 12, 2017 8:48 AM by Sunny Talwar RSS

    Qliksense - Rolling 13 Months Pivot Table

    Praveena Kumar

      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

          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