Need a logic for date field

[chinnu123]

Hi team,

I have a date field called open date by using this I need to derive the below fields.

Today

Yesterday

Current week this week from Monday

Last week means last week Monday to Friday data

Current month means this month start to till date data

Last month means jun 1st to June 30 data

Current quarter

Last quarter means April to June data

Current year means Jan 2017 to till now

Last year means Jan 2016 to december data

And after deriving above fields I need to take these all derived fields in single list box so user can select accordingly what he need.

Hope I was clear. Can any one please help on this.

  Thanks in advance

Thanks,

Chinnu.

[chinnu123]

Hi Tresco,

Thanks for your Help. Can you please provide me the for previous week previous month previous year also.

[chinnu123]

Hi ,

PFA !

[arvind_patil]

Hi Chinnu,

You can use below script for this:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min(TransactionDate) as minDate, 

               max(TransactionDate) as maxDate 

Resident LinKTable; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

Let CurrentDate=Num(Peek('maxDate', 0, 'Temp'));

Let CurrentYear=Year(Num(Peek('maxDate', 0, 'Temp')));

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS TransactionDate, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

Thanks,

Arvind Patil