can anyone point me to the solution on how to build the following year to date vs previous year?
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, weektodate (WTD), monthtodate (MTD), quarterlytodate (QTD), and yeartodate (YTD) vs. the previous year
Best Answer

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.
1
Answers

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!
1 
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()) thenTotalRevenue
END1 Complete Current Month (LY)
case when YEAR(
Simple_Date
) =YEAR(CURDATE()) 1 and MONTH(Simple_Date
) = MONTH(CURDATE()) thenTotalRevenue
END1 Complete Current Quarter (LY)
case when YEAR(
Simple_Date
) =YEAR(CURDATE()) 1 and QUARTER(Simple_Date
) = QUARTER(CURDATE()) thenTotalRevenue
END1 Complete Current Year (LY)
case when YEAR(
Simple_Date
) = YEAR(CURDATE()) 1 thenTotalRevenue
ENDpending to get Yesterday (LY)
but I need to get the partial totals, for example, today (5152024) the report needs to show for
Previous Day (LY) [5142023]
WTD (LY) is the sum of the totals from dates [5142023 to Previous Day (LY)]
MTD (LY) is the sum of the totals from dates [5012023 to Previous Day (LY)]
QTD (LY) is the sum of the totals from dates [4012023 to Previous Day (LY)]
YTD (LY) is the sum of the totals from dates [1012023 to Previous Day (LY)]
i will continue sharing the updates until is complete
0 
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.
1 
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) THENTotalRevenue
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))
ANDSimple_Date
< DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THENTotalRevenue
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())
THENTotalRevenue
END)QTD (LY)
SUM(CASE
WHEN YEAR(Simple_Date
) = YEAR(CURRENT_DATE())  1
AND QUARTER(Simple_Date
) = QUARTER(CURRENT_DATE())
ANDSimple_Date
< DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THENTotalRevenue
END)YTD (LY)
SUM(CASE
WHEN YEAR(Simple_Date
) = YEAR(CURRENT_DATE())  1
ANDSimple_Date
< DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
THENTotalRevenue
END)
the next step is to calculate the difference between the Current Year and the Last Year1 
now, put all the totals together in a friendly way
1 
Final version, Working
1
Categories
 All Categories
 1.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 288 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 94 SQL DataFlows
 594 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 670 Beast Mode
 42 App Studio
 34 Variables
 645 Automate
 165 Apps
 434 APIs & Domo Developer
 42 Workflows
 4 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 378 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 5 Software Integrations
 109 Manage
 106 Governance & Security
 8 Domo University
 23 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 95 Community Announcements
 4.8K Archive