35 Replies Latest reply: Nov 13, 2017 9:59 AM by LUIGI ZANETTI RSS

    Rolling sum on Qlik Sense

    LUIGI ZANETTI

      Hi all,

       

      I need to build a "rolling sum" plot (or table as well), i. e. a sum that month by month runs on the latest 12 months.

       

      More precisely, I have a graph that shows a variable (the number of sold products) month by month.

      The other variable I want to show is the sum of sold products on one year up to that month.

       

      The result should be a plot showing (month by month) the units sold in that month (e.g. March 2017) and the units sold on one year that far (e.g. April 2016 - March 2017).

       

      I hope the request is clear.

      Any help would be very appreciated.

       

      Thanks, LuigiZ

        • Re: Rolling sum on Qlik Sense
          Aehman K

          Sum({<Num_Month = {$(=Max(Num_Month)-1)}, Year = {$(=Max(Year)-1)} >} Units_Sold)

          Not a perfected expression cus by default what should be shown?

          Current year sales or previous year sales?

          And the selection should only happen if Month is selected?

           

          if(getselectedcount(Month)>0,

          Sum({<Num_Month = {$(=Max(Num_Month)-1)}, Year = {$(=Max(Year)-1)} >} Units_Sold))

           

          Maybe this would work...? Num month should be a numeric field and year as well.

            • Re: Rolling sum on Qlik Sense
              LUIGI ZANETTI

              Hi Aehman,

               

              the first formula you provided works but it gives the same result as:

              Sum(Units_Sold)

              What I wanted to do, instead, is to create  sum of Units_Sold on the last 12 months, month by month (the so called Rolling sum).

               

              Something like this:

               

              Sum(FROM Date.autoCalendar.MonthsAgo = 12 TO Date.autoCalendar.YearMonth)

               

              But I don't know how to set these two ends of the sum

               

              Thanks

            • Re: Rolling sum on Qlik Sense
              beck bakytbek

              Hi Luugi,

               

              check this: https://qlikviewandsql.wordpress.com/tag/cumulative-total/

               

              i hope that helps

               

              Beck

              • Re: Rolling sum on Qlik Sense
                LUIGI ZANETTI

                Complete information.

                I created a table in the same app showing the same result of the plot, but in numeric way (both have the same issue).

                 

                My plot have

                 

                DIMENSION = Date.Auttocalendar.YearMonth

                MEASURE = Sum(Quantity)

                 

                and it correctly shows me the total number of items sold per each month.

                 

                I'm really not able to show the units sold over 1 year:

                 

                Oct 2017 to show units sold from Nov,1 2016 to Oct, 31 2017

                Nov 2017 to show units sold from Dec, 1 2016 to Nov, 30 2017

                Dec 2017 to show units sold from Jan, 1 2017 to Dec, 31 2017

                 

                My fields are DATE for the dates and QUANTITY for the number of units

                  • Re: Rolling sum on Qlik Sense
                    Aehman K

                    I'm really not able to show the units sold over 1 year:

                     

                    Oct 2017 to show units sold from Nov,1 2016 to Oct, 31 2017

                    Nov 2017 to show units sold from Dec, 1 2016 to Nov, 30 2017

                    Dec 2017 to show units sold from Jan, 1 2017 to Dec, 31 2017

                    You want to how previous year sales upon selection of a particular field or by default?

                     

                    You can create a Last Year Flag in back end, use that Flag =1 in expression to show the sales of PYTD.

                     

                    Maybe I'm wrong, this looks like rolling year with month

                    Can you post a sample data?

                     

                    Thanks

                      • Re: Rolling sum on Qlik Sense
                        LUIGI ZANETTI

                        @Aehman

                         

                        I want the sales over 12 month, up to the indexed month by default. The, if I select a product, he plot should show sales restricted to that product.

                         

                        It's neither about sales of PYTD, nor sales YTD, it's sales on 12 months backwards.

                         

                        A sample data looks like this:

                          

                        QuantityPosting DateProduct
                        331/07/2017prod A
                        2028/07/2017prod A
                        5028/07/2017prod A
                        428/07/2017prod A
                        927/07/2017prod A
                        726/07/2017prod B
                        326/07/2017prod B
                        1626/07/2017prod B
                        424/07/2017prod B
                        3021/07/2017prod C
                        521/07/2017prod C
                        519/07/2017prod C
                        718/07/2017prod C
                        5418/07/2017prod A
                        618/07/2017prod A
                        118/07/2017prod A
                        118/07/2017prod B
                        204/07/2017prod B
                        121/07/2017prod C
                        113/07/2017prod C
                        7529/07/2017prod D
                        4028/07/2017prod D
                        528/07/2017prod D
                        528/07/2017prod D
                        627/07/2017prod D
                        126/07/2017prod B
                        326/07/2017prod B
                        526/07/2017prod B

                        for monthly sold product:

                        DIMENSION: PostingDate.autoCalendar.YearMonth

                        MEASURE: Sum(Quantity)

                         

                        I need to create a measure for the "Products on market" sold over 12 months backwards, month by month:

                      • Re: Rolling sum on Qlik Sense
                        omar bensalem

                        Create your own master calendar: (it's always better to build ur own calendar, it's a preference thing)

                        Just copy and paste this:

                        Let vMinDate=num(MakeDate(2014,12,31));

                        //Let vMinDate= AddYears(today(),-2);

                        Let vMaxDate=Today()+30;

                        NoConcatenate

                        [D_Calendar_TMP]:

                        Load date($(vMinDate)+ RowNo()) as [Calendar date]

                        AutoGenerate $(vMaxDate)-$(vMinDate);

                        NoConcatenate

                        [D_Calendar_TMP2]:

                        Load RowNo() as [%Date SEQ]

                        ,AutoNumber(date([Calendar date], 'DD/MM/YYYY'),'%Date ID') as [%Date ID]

                        ,date([Calendar date], 'DD/MM/YYYY') as [Calendar date]

                        ,Day([Calendar date]) as [Day of date]

                        ,'W' & right(WeekName([Calendar date],0,-4), 2) as [week date]

                        ,'Q' &  Num(ceil(month([Calendar date])/3), '00') as [quarter date]

                        , right(WeekName([Calendar date],0,-4), 2) as [Week of date]

                        ,Month([Calendar date]) as [Month of date]

                        ,QuarterName([Calendar date]) as [Quarter of date]

                        ,Year([Calendar date]) as [Year of date]

                        , right(WeekName([Calendar date],0,-4), 2) &'/'&Year([Calendar date]) as [Year week date]

                        ,date(MonthStart([Calendar date]),'MMM YYYY') as [Year month date]

                        ,num( Year([Calendar date])   ) & '- Q' &  Num(ceil(month([Calendar date])/3), '00') as [Year quarter date]

                           ,WeekStart([Calendar date],0,-4) as [Week start date]

                        ,WeekEnd([Calendar date],0,-4) as [Week end date]

                        ,MonthStart([Calendar date]) as [Month start date]

                        ,MonthEnd([Calendar date]) as [Month end date]

                        ,QuarterStart([Calendar date]) as [Quarter start date]

                        ,QuarterEnd([Calendar date]) as [Quarter end date]

                        ,YearStart([Calendar date]) as [Year start date]

                        ,YearEnd([Calendar date]) as [Year end date]

                        ,AutoNumber(WeekEnd([Calendar date],0,-4) , '%Year week SEQ') as [%Year week SEQ]

                        ,AutoNumber(Num(Month([Calendar date]), '00')&'/'&Year([Calendar date]), '%Year month SEQ') as [%Year month SEQ]

                        ,AutoNumber(Num(QuarterName([Calendar date]), '00')&'/'&Year([Calendar date]), '%Year quarter SEQ') as [%Year quarter SEQ]

                        ,if(monthstart(date([Calendar date],'MM/YYYY'))= monthstart(date(Today() ,'MM/YYYY')) ,monthstart(date(AddMonths(Today(),-2),'MM/YYYY')), monthstart(date(AddMonths([Calendar date] ,-1),'MM/YYYY'))) as [Month Fact]

                        Resident D_Calendar_TMP;

                         

                         

                        join(D_Calendar_TMP2)

                        Load max([%Date ID]) as [%Date ID]

                        ,1 as [Week end flag]

                        Resident D_Calendar_TMP2

                        Group by [Week end date];

                         

                         

                        join(D_Calendar_TMP2)

                        Load max([%Date ID]) as [%Date ID]

                        ,1 as [Month end flag]

                        Resident D_Calendar_TMP2

                        Group by [Month end date];

                         

                         

                        join(D_Calendar_TMP2)

                        Load max([%Date ID]) as [%Date ID]

                        ,1 as [Quarter end flag]

                        Resident D_Calendar_TMP2

                        Group by [Quarter end date];

                         

                         

                        join(D_Calendar_TMP2)

                        Load max([%Date ID]) as [%Date ID]

                        ,1 as [Year end flag]

                        Resident D_Calendar_TMP2

                        Group by [Year end date];

                         

                         

                        join(D_Calendar_TMP2)

                        Load min([%Date ID]) as [%Date ID]

                        ,1 as [Week start flag]

                        Resident D_Calendar_TMP2

                        Group by [Week start date];

                         

                         

                        join(D_Calendar_TMP2)

                        Load min([%Date ID]) as [%Date ID]

                        ,1 as [Month start flag]

                        Resident D_Calendar_TMP2

                        Group by [Month start date];

                         

                         

                        join(D_Calendar_TMP2)

                        Load min([%Date ID]) as [%Date ID]

                        ,1 as [Quarter start flag]

                        Resident D_Calendar_TMP2

                        Group by [Quarter start date];

                         

                         

                        join(D_Calendar_TMP2)

                        Load min([%Date ID]) as [%Date ID]

                        ,1 as [Year start flag]

                        Resident D_Calendar_TMP2

                        Group by [Year start date];

                         

                         

                        NoConcatenate

                        D_Calendar:

                        Load [%Date SEQ]

                        ,[%Date ID]

                        ,[Calendar date]

                        ,[Day of date]

                        ,[Week of date]

                        ,[Month of date]

                        ,[Quarter of date]

                        ,[Year of date]

                        ,[Year week date]

                        ,[Year month date]

                        ,[week date]

                        ,[quarter date]

                        ,[Year quarter date]

                        ,[Week start date]

                        ,[Week end date]

                        ,[Month start date]

                        ,[Month end date]

                        ,[Quarter start date]

                        ,[Quarter end date]

                        ,[Year start date]

                        ,[Year end date]

                        ,[%Year week SEQ]

                        ,[%Year month SEQ]

                        ,[%Year quarter SEQ]

                        ,[Month Fact]

                            ,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Month start date],'DD/MM/YYYY')) + 1) AS [Month Diff]

                            ,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Quarter start date],'DD/MM/YYYY')) + 1) AS [Quarter Diff]

                            ,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Year start date],'DD/MM/YYYY')) + 1)  AS [Year Diff]   

                        ,if(WeekEnd([Calendar date])=WeekEnd(Date(Today()-1,'DD/MM/YYYY')),Date(Today()-1,'DD/MM/YYYY'), WeekEnd([Calendar date])) as [Week end date 2]

                        ,if(MonthEnd([Calendar date])=MonthEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(MonthEnd([Calendar date]),'MMM YYYY') ) as [Month end date 2]

                        ,if(QuarterEnd([Calendar date])=QuarterEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(QuarterEnd([Calendar date]),'MMM YYYY') ) as [Quarter end date 2]

                        ,if(YearEnd([Calendar date])=YearEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(YearEnd([Calendar date]),'MMM YYYY') ) as [Year end date 2]

                         

                        //,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Week end flag]) as [Week end flag]

                         

                        , if(date(floor( WeekEnd([Calendar date],0,-4)))=date(floor(WeekEnd(Today()-1,0,-4))),

                                    if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Week end flag]) as [Week end flag]

                         

                         

                         

                        ,if(date(floor( MonthEnd([Calendar date])))=date(floor(MonthEnd(Date(Today()-1,'DD/MM/YYYY')))),

                                    if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Month end flag]) as [Month end flag]

                         

                         

                         

                        ,if( date(floor(QuarterEnd([Calendar date])))=date(floor(QuarterEnd(Date(Today()-1,'DD/MM/YYYY')))),

                                 if( [Calendar date]=Date('$(vCheckRevenuDate)','DD/MM/YYYY'),1) ,[Quarter end flag] )  as [Quarter end flag]

                                

                                

                                

                                

                        //,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Year end flag]) as [Year end flag]

                         

                        , if(date(floor( YearEnd([Calendar date])))=date(floor(YearEnd(Date(Today()-1,'DD/MM/YYYY')))),

                                    if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Year end flag]) as [Year end flag]

                         

                         

                        , [Week start flag]

                        , [Month start flag]

                        , [Quarter start flag]

                        , [Year start flag]

                        // ,if(date(floor(MonthEnd([Calendar date])))=date(floor(MonthEnd(Date(Today()-1,'DD/MM/YYYY')))),

                        // if([Calendar date]=Date('$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Month start flag]  )  as [Month start flag]

                        ,AutoNumber(Num(Month([Month Fact]), '00')&'/'&Year([Month Fact]), '%Year month fact SEQ') as [%Year month fact SEQ]

                        Resident D_Calendar_TMP2;

                         

                         

                         

                         

                        Drop table D_Calendar_TMP, D_Calendar_TMP2;

                         

                         

                        DONT't change a thing

                         

                        Then, in your fact table, in which you have the date field; do as follow:

                        AutoNumber(date(YourDateField, 'DD/MM/YYYY'),'%Date ID') as [%Date ID]


                        Now: as an expression for ur rolling month:


                        use this:

                        sum({<[Calendar date]=,[Month of date]=, [Year of date]=, [%Year month SEQ] = {"> $(= max([%Year month SEQ]) - 12)  <= $(=[%Year month SEQ])"}>}QUANTITY)


                        and a dimension use :

                        date([Month end date 2], 'MMM-YYYY')


                        Now, when you choose a Mars 2017 for example, you'll have:



                        Capture.PNG

                      • Re: Rolling sum on Qlik Sense
                        omar bensalem

                        If you want cumulative rolling: please refer to this:

                        Calculating rolling n-period totals, averages or other aggregations

                         

                        and try to use this:

                        sum( aggr( rangesum( above( sum(QUANTITY),0,12) ),Month))

                        • Re: Rolling sum on Qlik Sense
                          omar bensalem

                          Or try sthing like:

                           

                          sum({<Date.Autocalendar.YearMonth=,Date.Autocalendar.Year=,Date.Autocalendar.Month=,Date.Autocalendar.Date=,

                          Date.Autocalendar.Month={">=$(=addMonths(max(Date.Autocalendar.Date),-12))<=$(=max(Date.Autocalendar.Date))"}>}Quantity)

                          • Re: Rolling sum on Qlik Sense
                            omar bensalem

                            and please refer to this thread:

                            YTD, MTD issue

                            where I tried to explain step by step some of the basc time functions in Qlik !

                             

                            Hope these could help you Luigi !

                             

                            Omar BEN SALEM

                            • Re: Rolling sum on Qlik Sense
                              Andrea Gigliotti

                              Hi Luigi,

                              For your request you should use below expression:

                               

                              =sum( {< Date = {">=$(=MonthStart(AddMonths(Today(), -12)))<=$(=MonthEnd(AddMonths(Today(), -1)))"} >} Amount )

                               

                              what above for time period: nov '16 - oct '17