Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Hi
You may find a better approach for your requiermment in this post :
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
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)>>>>>>)<<<<<<
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;
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
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.