Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
varMinDate = Num(Peek('OrderDate', 0,'Orders'))
;
LET
varMaxDate = Num(Peek('OrderDate', -1
,
'Orders'
))
;
LET
varToday = num(today())
;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)
- rowno() + 1 AS DateNumber
,
date
($(varMinDate) - rowno() + 1)
AS
TempDate
AUTOGENERATE
$(varMaxDate)
- $(varMinDate) + 1
;
//*************** Master Calendar ***************
MasterCalendar:
LOAD
TempDate AS OrderDate
,
Week
(TempDate) AS Week
,
Year
(TempDate) AS Year
,
Month
(TempDate) AS Month
,
Day
(TempDate) AS Day
,
Weekday
(TempDate) AS WeekDay
,
'Q'
& ceil(month(TempDate) / 3) AS Quarter
,
Date
(monthstart(TempDate), 'MMM-YYYY') AS MonthYear
,
Week
(TempDate)&'-'&Year(TempDate) AS WeekYear
,
inyeartodate
(TempDate, $(varToday), 0) * -1 AS CurYTDFlag
,
inyeartodate
(TempDate, $(varToday), -1) * -1 AS
LastYTDFlag
RESIDENT
TempCalendar
ORDER
BY TempDate
ASC;
DROP
TABLE
TempCalendar;
regards,
Abdalla
Hi
on your application you use
let varMinDate = Num(Peek('OrderDate', 0,'Orders'));
do you have OrderDate filed on table call Orders. if not you should replace it with the filed you need to build your calender base on it. 🙂
Hi,
I have Order Date field but the thing is that ,,when I execute this code it shows me years not included in my Orders. Or it 's trim years this is thing bro.
And also what's is the temporary table and why I have to create means what is benefits of it.
Regards,
Abdalla
Hi there,
The point of the temporary table is to get a list of all possible dates between the earliest and latest dates in your data. This will allow you to plot a line graph over time with the time axis being correct (rather than being skewed by missing values).
If you do not require the missing dates to have the various permutations worked out for you can just do the various functions directly to the OrderDate field in the Orders table.
If you are only wanting dates within a specified range then replace the two peek statements (which are getting max and min) with hard coded date values.
Hope that helps.
Regards,
Steve