Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi does anyone have an example or a calender with CTD or cycle to date
example if you have
Cycle one Jan>Feb>Mar>Apr
Cycle two May>Jun>Jul>Aug
Cycle three Sep>Oct> Nov>Dec
i was thinking sometning in the lines of the below unless someone has a better idea
FOR i = 0 to 0
$(vTable)_tmp_CTD:
LOAD
*,
Dual(Quarter & '-' & Year, Date(QuarterStart(Addmonths(Date, $(i))), 'MMM-YY')) as [Year Quarter]
;
LOAD Distinct
%Calendar_Key,
Addmonths(%Calendar_Key, $(i)) as Date,
Num(Addmonths(%Calendar_Key, $(i))) as [Date Num],
Dual(Month(Addmonths(%Calendar_Key, $(i))), Num(Month(Addmonths(%Calendar_Key, $(i))))) as Month,
Dual('Q' & Ceil(Month(Addmonths(%Calendar_Key, $(i)))/3), Ceil(Month(Addmonths(%Calendar_Key, $(i)))/3)) AS Quarter,
Dual('C' & Ceil(Month(Addmonths(%Calendar_Key, $(i)))/4), Ceil(Month(Addmonths(%Calendar_Key, $(i)))/3)) AS Cycle,
Year(Addmonths(%Calendar_Key, $(i))) as Year,
Date(MonthStart(Addmonths(%Calendar_Key, $(i))), 'MMM-YY') as [Year Month],
Num(MonthStart(Addmonths(%Calendar_Key, $(i)))) as [Year Month Num],
Dual('CTD',1) as Timeframe,
'Cycle-To-Date' as [Timeframe Displayed],
1 as CTD_CY_TMP
Resident
$(vTable)_tmp
Where
Num(Addmonths(%Calendar_Key, $(i))) <= $(vMaxDate)
and Num(AddMonths(%Calendar_Key, $(i))) >= $(vMinDate)
;
NEXT
Concatenate ($(vTable))
LOAD
*
Resident
$(vTable)_tmp_CTD
;
I managed to get it working
using the below
Dual('C' & Ceil(Month(Addmonths(%Calendar_Key, $(i)))/4), Ceil(Month(Addmonths(%Calendar_Key, $(i)))/4)) AS Cycle,
in a master calender
I managed to get it working
using the below
Dual('C' & Ceil(Month(Addmonths(%Calendar_Key, $(i)))/4), Ceil(Month(Addmonths(%Calendar_Key, $(i)))/4)) AS Cycle,
in a master calender