Hi ChenjingLyu,
I hope below solutions will meet your requirements, kindly check these calculations
Max Date:
maxOver({your date field}, [ ], PRE_AGG)
Between dateDiff(), addDateTime(), truncDate(), and extract() you can define whatever periods you want (and there are often multiple ways to do each one, these are just some proven examples).
Please create Parameter ( Name it as AsOfDate ) by selecting fixed date option, then try below calculations
Today:
ifelse(dateDiff({order_date},${AsOfDate}) = 0,sales,0)
Yesterday:
ifelse(dateDiff({order_date},${AsOfDate}) = 1,sales,0)
Last 90 Days:
ifelse(dateDiff({order_date},${AsOfDate}) <=90, sales,0)
Last 2 Weeks:
ifelse(dateDiff({order_date},${AsOfDate},"WK") <=2, sales,0)
This year (aka Year to Date, YTD):
ifelse(dateDiff({order_date},${AsOfDate},"YYYY") = 0 AND {order_date}<=${AsOfDate}, sales, 0)
Last year:
ifelse(dateDiff({order_date},${AsOfDate},"YYYY") = 1, sales, 0)
Last year to date (aka LYTD):
ifelse(dateDiff({order_date},${AsOfDate},"YYYY") = 1 AND {order_date} <= addDateTime(-1,"YYYY",${AsOfDate}),sales,0)
This month (aka Month to Date, MTD):
ifelse(dateDiff({order_date},${AsOfDate},"MM") = 0 AND {order_date}<=${AsOfDate}, sales, 0)
Last month:
ifelse(dateDiff({order_date},${AsOfDate},"MM") = 1, sales, 0)
Same month last year:
ifelse(dateDiff({order_date},${AsOfDate},"MM") = 12, sales, 0)
Last month to date (aka LMTD):
ifelse(dateDiff({order_date},${AsOfDate},"MM") = 1 AND {order_date} <= addDateTime(-1,"MM",${AsOfDate}),sales,0)
Month to date last year (aka LYMTD):
ifelse(dateDiff({order_date},${AsOfDate},"MM") = 12 AND {order_date} <= addDateTime(-1,"YYYY",${AsOfDate}),sales,0)
This Quarter:
ifelse(dateDiff({order_date},${AsOfDate},"Q") = 0, sales, 0)
Last Quarter:
ifelse(dateDiff({order_date},${AsOfDate},"Q") = 1, sales, 0)
Same Quarter Last Year:
ifelse(dateDiff({order_date},${AsOfDate},"Q") = 4, sales, 0)
MoM % Diff:
(sum({This Month}) - sum({Last Month})) / sum({Last Month})
YoY % Diff:
(sum({This Year}) - sum({Last Year})) / sum({Last Year})
Monthly YoY % Diff:
(sum({This Month}) - sum({This Month Last Year})) / sum({This Month Last Year})
QoQ% Diff:
(sum({ This Quarter}) - sum({Last Quarter}))/sum({Last Quarter}))