9 Replies Latest reply: Nov 16, 2017 3:24 PM by Layla Mar RSS

    What is missing here to show Rolling 13 months ?

    Layla Mar

      MonthDates : 1/2015 - 9/2017

       

      expression :  sum ( Leads)

       

      Order Year = 2015,2016,2017

       

      Order Month = 1- 12

       

      If MonthDates is my date field  do I also need to construct another period ID ?

       

      How do I show rolling 13 months based on current selection ?

        • Re: What is missing here to show Rolling 13 months ?
          Ricardo Gerhard

          Dear Layla,

          Can you explain a bit more what do you want to do?

          • Re: What is missing here to show Rolling 13 months ?
            Wallo Atkinson

            Qlikview is not actually reading it as a date.  I would think it's reading it as a string.

            I think you'd be better off making the MonthDates column show the first day of every month.

            1/1/2016,2/1/2016,3/1/2016 etc.

            THen qlik will read as a date/integer.

             

            then you could do something like

            sum ({$<MonthDates={'>=1/1/2015 <= 9/1/2016'} Leads)

            • Re: What is missing here to show Rolling 13 months ?
              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