Hii
//-------------------------------------------1 step-------------------------------------------------------//
// create table and extract min and max date
// define Min Max Date
min_max:
Load
Min(Delivery_Date) as minDate,
Max(Delivery_Date) as maxDate
Resident SALESDETAILS;
//-------------------------------------------2 step-------------------------------------------------------//
// create variable and peek dates of min max from loaded table (min_max)
Let vMinDate = Peek('minDate',0,'min_max');
Let vMaxDate = Peek('maxDate',0,'min_max');
//after fetching min max dates from min_max --> no use of it so drop table
drop table min_max;
//-------------------------------------------3 step-------------------------------------------------------//
// creating master calendar & Extract all the necessary date columns required for the dashboards between min and max
Master_Calendar:
Load
MonthName(Delivery_Date) as MonthName,
QuarterName(Delivery_Date) as QuarterName,
YearName(Delivery_Date) as YearName,
'Q'&Ceil(Month(Delivery_Date)/3) as Quarter,
Year(Delivery_Date) as Year,
Month(Delivery_Date) as Month,
WeekDay(Delivery_Date) as WeekDay,
Week(Delivery_Date) as Week,
Day(Delivery_Date) as Day,
Year(Delivery_Date) as yearDate,
'HY'&Ceil(Month(Delivery_Date)/6) as HalfYear
;
//-------------------------------------------4 step-------------------------------------------------------//
//looping--> from minimum date till maximum date rech
Load
Date($(vMinDate) + IterNo() - 1) as Delivery_Date
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
Hope this helps,
help user find answers ! don't forget to mark a solution that work for you and click the like button!
