DateRangeLogic

[abhaysingh]

Hey Guys,

I m trying to make Date Range in My Calendar, but Its Not Working Properly..

Actually i want

if( Date = today(), 'today',

if( Date= today()-1, 'Yesterday',

if( Date>= Weekend(Date) and Date <= WeekStart(Date), 'This Week'))) and so on for 'last 30 Days', 'this Month', 'this Quarter', 'last 6 Month', 'last 12 Month'

Please Advice..

thanks

[sujeetsingh]

you can use the master calender script and just make changes as per your need.

[Gysbert_Wassenaar · Accepted solution]

Your excel file doesn't contain dates,  but string values. You first need to turn those into dates using the date# function. Something like:

LOAD *,

  if( Date = today(), 'Today',

       if( Date= today()-1, 'Yesterday',

            if( InWeek(Date,Today(),0), 'This Week',

                 if( InWeek(Date,Today(),-1), 'Last Week',

                      if( InMonth(Date,Today(),0),'This Month',

                           if( InMonth(Date,Today(),-1),'Last Month',

                                if( InQuarter(Date,Today(),0),'This Quarter',

                                     if( InQuarter(Date,Today(),-1),'Last Quarter' )))))))) as Period;

LOAD Date#(Date,'DD/MM/YYYY') as Date,

     Month,

     MonthName,

     Quarter,

     QuarterName,

     Week,

     WeekDay,

     WeekName,

     WeekYear,

     Year,

     Day

FROM [CommunityDateTest.xls] (biff, embedded labels, table is Sheet1$);

[martin_dideriks]

Gysbert. Are you sure this will work?

If Yesterday is a sunday, this should also be part of "Last week" and probably "This month".

The if-statement will stop evaluating when the first criteria is met, which means that a date only will be part of one period.

Another option is to load the periods one at the time.

Periods:

LOAD

     Date,

     'Last Month' AS Period

RESIDENT MyDateTable

WHERE Date>MonthStart(AddMonths(Today(),-1)) AND Date<MonthStart(Today())

CONCATENATE (Period)

LOAD

........

//Martin

[abhaysingh]

Hi Gysbert,

could you please suggest me with last 30 days, last 6 month and last 12 month logic in the above condition.