Previous Year YTD Aggregation functions

Question & Answer
, ,
1

Hi team,

Background in my analysis, I want to compare current year YTD revenue v.s. 2022 YTD revenue, in which I need 2023 Jan-March revenue data v.s. 2022 Jan-March revenue data.

I need to calculate dynamic YTD revenue for previous year (2022). I used this function:periodToDateSumOverTime(sum(ddc), {calendar_date}, MONTH). This formula turns out to the correct numbers but it gave me more month’s data than I expected. I only want 2022 revenue data from Jan - March.

Could anyone pls advice solutions to my analysis?

Thanks!

2

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}))

2 Likes