can anyone point me to the solution on how to build the following year to date vs previous year?

German_Rodriguez · 2024-05-15T13:35:41+00:00

There was an error rendering this rich post.

I have a feed that collects daily sales and units, I need to create a report that generates a table showing the following information

the previous day, week-to-date (WTD), month-to-date (MTD), quarterly-to-date (QTD), and year-to-date (YTD) vs. the previous year

Find more posts tagged with

Accepted answers

Sean_Tully

For MTD and WTD, you can use the dayofmonth() and dayofweek() functions to get the numerical value of the previous day in terms of week or month, and then compare to last year's to make sure the same value is less than or equal.

QTD and YTD can get tricky depending on if you're using a special fiscal year or not. If it's just the straight calendar year, the dayofyear() function should work. If not, you'll have to make some adjustments in the beast mode. Someone else might know how to do QTD in an easy way, but I think you'd have to define each quarter in the beast mode and then adjust the value you get from the dayofyear() function accordingly.

All comments

Sean_Tully

You will have to either build out beast modes for each metric, or build them out as formulas in Magic ETL. In essence, you'll be making a bunch of formulas that look like the one below, which would be for sales, previous day, this year:

sum(case when date_field = current_date() - 1 then sales_field end)

Sales, previous day, last year: would be:

sum(case when date_field = sub_date(current_date(), interval 1 year) - 1 then sales_field end)

And the YOY change would be a new beast mode taking the difference between the two formulas. Repeat down the line for each date iteration, adapting the comparison between your date_field and the current_date() function to fit your needs.

Hope this helps!

German_Rodriguez

https://community-forums.domo.com/main/discussion/comment/95846#Comment_95846

thank you for sharing, I was able to move a little forward but not to the end yet, hopefully, someone will be able to push me a little bit further,

checking the Last Year part I was able to get

1 Complete Current Week (LY)

case when YEAR(Simple_Date) =YEAR(CURDATE()) -1 and WEEK(Simple_Date) = WEEK(CURDATE()) then TotalRevenue END

1 Complete Current Month (LY)

case when YEAR(Simple_Date) =YEAR(CURDATE()) -1 and MONTH(Simple_Date) = MONTH(CURDATE()) then TotalRevenue END

1 Complete Current Quarter (LY)

case when YEAR(Simple_Date) =YEAR(CURDATE()) -1 and QUARTER(Simple_Date) = QUARTER(CURDATE()) then TotalRevenue END

1 Complete Current Year (LY)

case when YEAR(Simple_Date) = YEAR(CURDATE()) -1 then TotalRevenue END

pending to get Yesterday (LY)

but I need to get the partial totals, for example, today (5-15-2024) the report needs to show for

Previous Day (LY) [5-14-2023]

WTD (LY) is the sum of the totals from dates [5-14-2023 to Previous Day (LY)]

MTD (LY) is the sum of the totals from dates [5-01-2023 to Previous Day (LY)]

QTD (LY) is the sum of the totals from dates [4-01-2023 to Previous Day (LY)]

YTD (LY) is the sum of the totals from dates [1-01-2023 to Previous Day (LY)]

i will continue sharing the updates until is complete

Sean_Tully

German_Rodriguez

it works, LY is working the way I need it

the calculations are:

Previous Day (LY)

SUM(CASE WHEN DATE(Simple_Date) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 1 YEAR) THEN TotalRevenue END)

WTD (LY)

SUM(CASE
WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
AND WEEK(Simple_Date) = WEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR))
AND Simple_Date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THEN TotalRevenue
END)

MTD (LY)

SUM(CASE
WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
AND MONTH(Simple_Date) = MONTH(CURRENT_DATE())
AND DAYOFMONTH(Simple_Date) < DAYOFMONTH(CURRENT_DATE())
THEN TotalRevenue
END)

QTD (LY)

SUM(CASE
WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
AND QUARTER(Simple_Date) = QUARTER(CURRENT_DATE())
AND Simple_Date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THEN TotalRevenue
END)

YTD (LY)
SUM(CASE
WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
AND Simple_Date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THEN TotalRevenue
END)

the next step is to calculate the difference between the Current Year and the Last Year

German_Rodriguez

now, put all the totals together in a friendly way

German_Rodriguez

Final version, Working