Calendar -QTD

[krishna20]

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

[krishna20]

Any inputs pls??

[sunny_talwar]

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?

[krishna20]

Yes, right sunny. The flag should show 1 for all months of Jul, Aug, and Sep and rest will be 0.

[sunny_talwar]

I would do something like this

If(InQuarterToDate(SetDateYear(TempDate, Year(Today())), QuarterEnd(SetDateYear(Today(), Year(Today()))), 0), 1, 0) as InQuarterFlag

[sunny_talwar]

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

[krishna20]

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
;

[Brett_Bleess]

@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