I calculated mtd ytd day calculation in script .I tried it in set analysis but my application is getting bigger so we thought to implement mtd,ytd logic in script.
I got values for day ,but for mtd ytd i am not able to pull the data.
My aplication consist of calendar object ,list box and sum(Sales)
I'll select date and mtd or ytd or day from list box and corrresponding sum of sales will be displayed.
My code is below .
Cal:
LOAD Date ($(StartCalendar) + RecNo()-1) as CalDate
AutoGenerate ((today()-1) - $(StartCalendar)+1 );
//Left Join with no keys
Left Join (Cal)
Load CalDate as #_Date_NUM_KEY
Resident Cal;
//Bringing Accumulated YTD granule
NoConcatenate
Cal_TimeGranule:
Load *, 'YTD' as TimeGranule
Resident Cal where #_Date_NUM_KEY >= YearStart(CalDate) and #_Date_NUM_KEY <= CalDate;
//Bringing Accumulated MTD granule
Concatenate
Load *, 'MTD' as TimeGranule
Resident Cal where #_Date_NUM_KEY >= monthstart(CalDate) and #_Date_NUM_KEY <= CalDate;
//Bringing Accumulated Day granule
Concatenate
Load * , 'Day' as TimeGranule
Resident Cal
where #_Date_NUM_KEY = CalDate;
drop Table Cal;
CAL:
Load
CalDate,
// Date(SALES_DATE, 'DD/MM/YYYY') as SALES_DATE_1,
Month(CalDate) as SALES_MONTH,
Year(CalDate) as SALES_YEAR,
MonthName(CalDate) as SALES_MONTHNAME,
'Q' & Ceil(Month(CalDate)/3) AS SALES_QUARTER,
DayNumberOfYear(CalDate) as DayNumberOfYear,
Day(CalDate) as DayNumberOfMonth,
DayNumberOfQuarter(CalDate) as DayNumberOfQuarter,
