Is it possible to calculate ROAS and WoW change in the same beast mode formula?
I'm working on a pivot table and I want to show the % change of ROAS from last week vs. two weeks ago. My dataset does not contain a 'ROAS' column so I'm trying to calculate it within the WoW beast mode formula. If I breakout the formula below into two parts (LW ROAS vs. 2WA ROAS), the first part will correctly give me last week's ROAS but I'll get blank rows for the latter.
Columns are structured as: Week | Sales|ROAS|LW ROAS|2WA ROAS (Blank Rows)|WoW Difference
Here's the formula that I'm using:
(sum(case when day(date_
)-day(CURDATE()) and week(date_
)-week(CURDATE()) then sales
end)/ sum(case when day(date_
)-day(CURDATE()) and week(date_
)-week(CURDATE()) then spend
end)
/
sum(case when day(date_
)-day(CURDATE() - INTERVAL 7 day) and week(date_
)-week(CURDATE() - INTERVAL 7 day) then sales
end)/ sum(case when day(date_
)-day(CURDATE() - INTERVAL 7 day) and week(date_
)-week(CURDATE() - INTERVAL 7 day) then spend
end)) - 1
Best Answer
-
Hi @ccc123 ,
Here's my video on how to do rates:
and here's my video on time comparisons:
Just for you or anyone who wants to see for future reference
John Le
You're only one dashboard away.
Click here for more video solutions:
1
Answers
-
@ccc123 without using Domo formulas, sadly I'm having a hard time following the logic since each part of your WHEN statement does not really evaluates to a Boolean value (I know this is not mandatory, but surely helps making it clear for others to understand when you want things to happen or not), could you explain how you'd calculate ROAS and whether this is meant to be responsive to filters or always calculated from the full raw dataset information. If the later, you might be best doing this on a ETL and using a Rank and Window function with Lag to get the LW and 2WA ones.
0 -
@marcel_luthi the first part below calculates the last week ROAS and its always calculated from the raw dataset, which is why it almost looks the same except for the 'Spend' and 'Sales'. This part is working correctly and it returns last week's ROAS.
(sum(case when day(
date_
)-day(CURDATE()) and week(date_
)-week(CURDATE()) thensales
end)/ sum(case when day(date_
)-day(CURDATE()) and week(date_
)-week(CURDATE()) thenspend
end)The second part below calculates the ROAS from 2 weeks ago, this is the part where I'm having trouble with. I can't seem to find the solution to pull and calculate the ROAS from two weeks ago.
sum(case when day(
date_
)-day(CURDATE() - INTERVAL 7 day) and week(date_
)-week(CURDATE() - INTERVAL 7 day) thensales
end)/ sum(case when day(date_
)-day(CURDATE() - INTERVAL 7 day) and week(date_
)-week(CURDATE() - INTERVAL 7 day) thenspend
end)) - 1The '/' you see between the two parts is part of the WoW difference formula along with the '-1'.
"(current value/past value)-1".
Any articles or videos you recommend to do this with rank and window function? I'm fairly new to domo so I have idea what those are.
0 -
Hi @ccc123 ,
Here's my video on how to do rates:
and here's my video on time comparisons:
Just for you or anyone who wants to see for future reference
John Le
You're only one dashboard away.
Click here for more video solutions:
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive