Date Range in Load Script Qlik Sense

[Anonymous]

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,

[brunobertels]

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

[Anonymous]

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)>>>>>>)<<<<<<

[brunobertels]

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;

[rahulpawarb]

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 &amp; YTD (Current Year &amp; Previous Year)

Regards!

Rahul

[Anonymous]

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.