topic Re: Master Calender not showing all dates in App Development

Master Calender not showing all dates

Lionel_Maximus — Fri, 02 Apr 2021 07:50:21 GMT

Hello All,

I am using this master calendar:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(DELIVERY_DATE) as minDate,
max(DELIVERY_DATE) as maxDate
Resident FACT;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) As TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Right Join(FACT)
Load
TempDate AS DELIVERY_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

I want to calculate Sales Per Day :

(Sum(LPD))/$(MaxDeliveryDay)

Where $(MaxDeliveryDay) = ((Max(DELIVERY_DATE) - Min(DELIVERY_DATE)) +1 )

When i select 1-15 days $(MaxDeliveryDay) gives me 13 because there is no sale in 2 days but i want all 15 days....

How's that possible??

Regards,

Re: Master Calender not showing all dates

PrashantSangle — Mon, 04 Mar 2019 09:54:47 GMT

instead of max(DELIVERY_DATE) use Max(TempDate)

Note : for TempDate do small modification in your script.
add TempDate in master calender table.
,

Re: Master Calender not showing all dates

Lionel_Maximus — Mon, 04 Mar 2019 11:53:42 GMT

Hello,
Thans for your reply..
What do you mean by this sorry i couldn't understand??
"Note : for TempDate do small modification in your script.
add TempDate in master calender table."
Regards

Re: Master Calender not showing all dates

PrashantSangle — Mon, 04 Mar 2019 12:07:41 GMT

MasterCalendar:

Load
TempDate AS DELIVERY_DATE,
TempDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar;

Regards,

Re: Master Calender not showing all dates

Lionel_Maximus — Mon, 04 Mar 2019 13:10:17 GMT

didn't work 😕