Master Calendar flag for QTD for all Years in the data

[sudhakar_budde]

Hi ,

Can anybody help me with my requirement please?

I have a business requirement of creating a flag for QTD_All_Years.

If I choose QTD_All_Years flag then I should be able to show current QTD dates or Monthyear and also same for all previous years in the data set.

For example:

QTD   ---> Dates ( 01/10/2018 till 06/12/2018)--> MonthYear (Oct-18, Nov-18, Dec-18)

QTD_All_Years -->Dates (same date sets as above for each previous year)--> MonthYear( Same 3 periods as above for each year)

I attached a screen shot.

Is there any suggestion please?

Thanks & Regards

SB

[Mark_Little]

Hi
Probably something like

IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND DATE(TempDate,'DD/MM') <= DATE(TODAY(1),'DD/MM'),1) AS F_CAL_QTD,

Mark

[sudhakar_budde]

Hi Mark,

Yeah, we are almost there!

I have two observations here: 

1. I need the QTD (Till today only of current Quarter) and similar to all other years.

2. If we use the Date part DATE(TODAY(1),'DD/MM'), then will it also work for the leap year ?

Thanks 

SB

[Mark_Little]

Hi,
Been playing so try this
Calendar:
LOAD
....
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(TODAY(1)) AND DATE(TempDate) <= TODAY(1),1) AS F_CAL_QTD,
LEFT(DATE(TempDate),5) as TestJoin
Resident TABLE;

LEFT Join

LOAD
TestJoin,
1 as C_QTDALL
Resident Calendar
WHERE F_CAL_QTD = 1;

Sorry not sure about leap years, but in theory should be ok.

Mark

[sudhakar_budde · Accepted solution]

Hi Mark,

Thanks a lot for your time and effort!

I have modified your code as below and it's working now.

IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) and DayNumberOfYear(TempDate) <= DayNumberOfYear(Today(1)),1,0) as QTD_AllYears

Regarding the leap year, the max date of Feb i.e. DayNumberOfYear(TempDate)= 60 which is unique of that year. So hopefully there shouldn't be an issue on calculating the values for that year.

Thanks & Regards

SB