1 Reply Latest reply: Jul 12, 2013 9:52 PM by Sokkorn Cheav RSS

    How to show last 18 months of data from the current month

      Hi I'm  new to Qlikview and I'm trying to display the last 18 months of data from the current month and show it into line chart.

      Can anyone help me with this?


      Thanks

        • Re: How to show last 18 months of data from the current month
          Sokkorn Cheav

          Hi,

           

          To manage this requirement, we need to add more field in load script. Ex.

          [Data]:

          LOAD

               ...

               SaleAmount,

               Year(SaleDate)*12 + Month(SaleDate)     As [YM_Seq],

               SaleDate,

               Month(SaleDate)     As [SaleMonth],    

               ...

          From...

           

          Create a line chart with:

               1. Dimension: [SaleMonth]

               2. Expression: =Sum({$<[YM_Seq] = {">=$(=Max([YM_Seq])-18)<=$(=Max([YM_Seq]))"} >} [SaleAmount])

           

          Edit:

          If you want to calculate [SaleAmount] for last 18 months from your max month in your data table, you can use expression above. If you want to calculate last 18 months current month, then use below expression:

          =Sum({$<[YM_Seq] = {">=$(=Max([YM_Seq])-18)<=$(=(Year(Today())*12 + Month(Today())))"} >} [SaleAmount])

           

          Regards,

          Sokkorn