Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I need to include the QTD in the below calendar under the Period flag. Could anyone advise me how to implement this?
As I have 5yrs of data , QTD should reflect all these years. If it is not possible in the below script, advise any other logics in the master calendar.
Note: QTD,MTD,YTD should be in a single field.
Month:
LOAD * INLINE [
Month, Quarter, MonthDesc
1, Q1, Jan
2, Q1, Feb
3, Q1, Mar
4, Q2, Apr
5, Q2, May
6, Q2, Jun
7, Q3, Jul
8, Q3, Aug
9, Q3, Sep
10, Q4, Oct
11, Q4, Nov
12, Q4, Dec
];
Calendar:
LOAD Distinct Month, 'MTD' as Period Resident Fact;
Left Join (Calendar)
Load
Month, Quarter , Date(Date#(MonthDesc,'MMM'),'MMM') as MonthDesc
resident Month;
Concatenate
LOAD
IterNo() as Month,
MonthDesc,
'YTD' as Period,
Quarter
resident Calendar
While IterNo() <= Month;
Drop Table Month;
@sunny_talwar , @jagan , @Gysbert_Wassenaar
Thanks
Krishna
Any inputs pls??
You want QTD flag for each of the last 5 years? meaning, right now the flag will show 1 for all months of Jul, Aug, and Sep? and rest of the months will be 0?
Yes, right sunny. The flag should show 1 for all months of Jul, Aug, and Sep and rest will be 0.
I would do something like this
If(InQuarterToDate(SetDateYear(TempDate, Year(Today())), QuarterEnd(SetDateYear(Today(), Year(Today()))), 0), 1, 0) as InQuarterFlag
Here is a sample script
Table:
LOAD *,
If(InQuarterToDate(SetDateYear(TempDate, Year(Today())), QuarterEnd(SetDateYear(Today(), Year(Today()))), 0), 1, 0) as InQuarterFlag,
Month(TempDate) as MonthDesc,
Num(Month(TempDate)) as Month,
'Q' & Ceil(Month(TempDate)) as Quarter;
LOAD Date(MakeDate(Year(Today())-5) + IterNo() - 1) as TempDate
AutoGenerate 1
While MakeDate(Year(Today())-5) + IterNo() - 1 < MakeDate(Year(Today())+1);
Hi Bhai,
Thanks for lighting up on the issue.
I have modified my script with your logic given. But not getting expected output.
I need MTD, QTD, YTD all together in a single field.
QTD is mapping and now YTD and MTD is not mapping to the fact data.
Calendar:
LOAD *,
If(InQuarterToDate(SetDateYear(TempDate, Year(Today())), QuarterEnd(SetDateYear(Today(), Year(Today()))), 0), 'QTD') as PeriodScope,
Month(TempDate) as MonthDesc,
Num(Month(TempDate)) as Month,
'Q' & Ceil(Month(TempDate)/3) as Quarter;
LOAD Date(MakeDate(Year(Today())-5) + IterNo() - 1) as TempDate
AutoGenerate 1
While MakeDate(Year(Today())-5) + IterNo() - 1 < MakeDate(Year(Today())+1);
Concatenate
Load
If(Day(TempDate) <= Day(Today()) and Month(TempDate) = Month(Today()), 'MTD')as PeriodScope
Resident Calendar;
Concatenate
LOAD
IterNo() as Month,
'YTD' as PeriodScope
Resident Calendar
While IterNo() <= Month
;
@sunny_talwar Further information provided on requirement, any chance you can have another look and provide adjusted code to handle, this one is over my head? I have to say I am a bit confused on the requirement, hopefully it makes sense to you though.
Regards,
Brett