5 Replies Latest reply: Jan 25, 2017 8:44 AM by Salamon Musayev RSS

    Date Range in Load Script Qlik Sense

    Salamon Musayev

      If have a date field and would like to have a filed that would calculate last 6, 12, 18 , 24 months from the last date in the date field.

       

      I have found a code sample looking online and when I  implement it my last 12 month does not include the first 6 month, the same thing with 18 and 24.  It seems to calculate the 6 month between that period but I need it to be cumulative.  Like the last 12 full month including the first 6th month. 

       

      Below is my script. 

       

      The date field is "EpisodeStartDate"

       

      if(12*(Year(Today())-Year(EpisodeStartDate)) + Month(Today()) - Month(EpisodeStartDate) <= 6, 'Last 6 Months'

        if(12*(Year(Today())-Year(EpisodeStartDate)) + Month(Today()) - Month(EpisodeStartDate) <= 12, 'Last 12 Months',

          if(12*(Year(Today())-Year(EpisodeStartDate)) + Month(Today()) - Month(EpisodeStartDate) <= 18,'Last 18 Months',

              if(12*(Year(Today())-Year(EpisodeStartDate)) + Month(Today()) - Month(EpisodeStartDate) <= 24,'Last 24 Months',

                  if(12*(Year(Today())-Year(EpisodeStartDate)) + Month(Today()) - Month(EpisodeStartDate) <= 30,'Last 30 Months', 'Last 36 or More Months')

                   )

                 )

       

      Also the code runs of the todays date but how do I run it of the max date in the datefield.

       

      Please help.

       

      Thank you,

        • Re: Date Range in Load Script Qlik Sense
          bruno bertels

          Hi

           

          You may find a better approach for your requiermment in this post :

           

          6,12,18,24 months

           

          You should accapt it with your need

           

          May be somthing like this :

           

          // add Min and Max Date

          LET Start = num(min(EpisodeStartDate));

          LET End = num(max(EpisodeStartDate));

          LET NumOfDays = End - Start + 1;


          Date_src:

          LOAD

          $(Start) + Rowno() -1 as DateId

          AUTOGENERATE $(NumOfDays);

           

          Flag_Period:

          LOAD

          DateId ,

          if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -6) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag6Month,

          if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -12) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag12Month,

          if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -18) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag18Month,

          if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -24) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag24Month,

          if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -30) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag30Month,

          if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -36) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag36Month

          RESIDENT Date_src;

           

          Drop Table Date_src;


          hope this help,


          Bruno

            • Re: Date Range in Load Script Qlik Sense
              Salamon Musayev

              Hi Bruno,

               

              I am having an issue with

              LET Start = num(min(EpisodeStartDate));

              LET End = num(max(EpisodeStartDate));

               

              part of the script.

               

              Any ideas what the error below means?

               

               

              The following error occurred:

              Unexpected token: ')', expected nothing

              The error occurred here:

              LET Start = num(min(EpisodeStartDate)>>>>>>)<<<<<<

                • Re: Date Range in Load Script Qlik Sense
                  bruno bertels

                  Hi

                   

                  OK let's try like this

                   

                  // add Min and Max Date

                  LET Start = min(date(EpisodeStartDate));

                  LET End = max(date(EpisodeStartDate));

                  LET NumOfDays = $(#End) - $(#Start) + 1;


                  Date_src:

                  LOAD

                  date($(Start) + Rowno() -1) as DateId

                  AUTOGENERATE $(NumOfDays);


                  Flag_Period:

                  LOAD

                  DateId ,

                  if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -6) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag6Month,

                  if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -12) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag12Month,

                  if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -18) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag18Month,

                  if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -24) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag24Month,

                  if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -30) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag30Month,

                  if(DateId >= AddMonths(MonthStart(max(EpisodeStartDate)), -36) and DateId <= monthstart(max(EpisodeStartDate))-1, 1, 0) as Flag36Month

                  RESIDENT Date_src;

                   

                  Drop Table Date_src;

              • Re: Date Range in Load Script Qlik Sense
                Rahul Pawar

                Hello Salomon,

                 

                Hope you are doing well!

                 

                Please refer below solution:

                 

                Load the data on which you want to analyze. Very important to have the DateKey field in number format by using Floor(DateKey) AS DateNum. This will gives you numeric equivalent of the respective date. Post that you can derive the required expressions as given below:


                //6 Months
                Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-6)))) <=$(=Num(Max(DateKey)))"}>}Sales)
                
                //12 Months
                Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(YearStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Sales)
                
                //18 Months
                Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-18)))) <=$(=Num(Max(DateKey)))"}>}Sales)
                
                //24 Months
                Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddYears(Max(DateKey),-2)))) <=$(=Num(Max(DateKey)))"}>}Sales)
                
                


                For more details refer the attached application as well as link.


                Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)


                Regards!

                Rahul

                • Re: Date Range in Load Script Qlik Sense
                  Salamon Musayev

                  Hi  and thank you all for your suggestions.

                   

                  I have figured out a different way of doing this and wanted to know your opinion on it.

                   

                  I have created a master item dimension flag and this is the formula I used

                   

                  if((Today() - EpisodeStartDate) <= 180, 'Yes', 'No')

                   

                  Please let me know if I will run ino issues using this type of formula.

                   

                  Thank you.