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

Options
Member

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

• Contributor
Options

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.

• Contributor
edited May 15
Options

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!

• Member
Options

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

• Contributor
Options

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.

• Member
Options

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

• Member
Options

now, put all the totals together in a friendly way

• Member
Options

Final version, Working