4 Replies Latest reply: Jun 28, 2013 6:16 AM by tmyicecream RSS

    Rolling 12 months data

      Hi, I just started to use QlikView recently.

      Could you pls give me an advice about below expression? I want to show up rolling 12month's data.

       

       

      =if(sum(if([YearMonth]> DATA(ReloadTime(),'YYYYMM') ()-12, [Cost])), sum(if([YearMonth]> DATA(ReloadTime(),'YYYYMM') ()-12, [Cost])))

       

       

      Thank you so much for your help in advance.

        • Re: Rolling 12 months data
          Vishwaranjan Kumar

          FOR RELOAD TIME,

          TRY THIS CODE

          date(date#(ReloadTime(),'MM/DD/YYYY hh:mm:ss TT'),'YYYY-MM')

           

          FOR ROLLING 12 MONTH TRY THIS CODE--

           

          sum({$<Date = {">=$(=MonthStart(Max(Date),-12))<=$(=MonthEnd(Max(Date),-1))"},Year=,Month=>}Sale)

            • Re: Rolling 12 months data

              Thank you for your advice.

              I'm sorry, I'm not good at to manage it. Could you pls let me know more details?

              Here I add more info.

               

              EX: Data base

              YearMonth  Notif   Name Notif   Cost

              201205     12345    AAAAA       100EUR

              201301     12345    AAAAA       200EUR

              201302     12345    AAAAA       525EUR

              201305     12345    AAAAA       300EUR

              201201     11111    BBBBB       350EUR

              201301     11111    BBBBB       250EUR

              201305     11111    BBBBB       300EUR

               

              and I want to show up on QlikView below style.

              Picture1.png

            • Re: Rolling 12 months data
              Prem Kumar Thangallapally

              can you please explain the question in detail with example

                • Re: Rolling 12 months data

                  Every month, cost date by each notification No. is updating.

                  Ex:

                  YearMonth

                  Notification No.

                  Notification Name

                  Cost(EUR)

                  201203

                  1000

                  A

                  700

                  201211

                  1000

                  A

                  1000

                  201302

                  1000

                  A

                  525

                  201305

                  1000

                  A

                  800

                  201306

                  1000

                  A

                  950

                  Added on July

                  201201

                  1001

                  B

                  350

                  201301

                  1001

                  B

                  300

                  201305

                  1001

                  B

                  850

                  201306

                  1001

                  B

                  150

                  Added on July

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  We would like to see the `sum` of the cost which are only over `Total 2,000EUR`  by notification No. So I am using below expression.

                  =sum(if([Cost]>=2000,[Cost]))

                   

                  And I would like to add one more condition which is rolling 12months.

                  Look at the Notification No `1000`, there are 5 different cost reports(=5 months).

                  However first one is little old data.

                  We just want to see if we open the QlikView on July 10th , each Notification No.’s cost total of rolling 12 months is calculate automatically without selecting YearMonth(201205~201306).

                  We want to see like below

                  Notification No.

                  Notification Name

                  Cost

                  1000

                  A

                  3275

                  *Notification No. 1001 is not showing up, because Cost total is less than 2000EUR.

                   

                   

                  So based on ReloadTime, we would like to see the rolling 12month cost total.

                   

                  Hope you understand what I mean...Thank you so much for your help!