Cumulative Total Measures
I am looking for help understanding how to do cumulative time intelligence calculations ( MTD, QTD, YTD ) in Domo. I have a dataset with customer reviews (review ID, date, source, Stars, comment ) and am trying to achieve a cumulative running total of the average star rating. I'm used to PowerBI where time intelligence measures are easily achievable with dax and am hoping Domo has the same functionality.
Avg Rating = AVG(Review Stars
)
Avg Rating RT = AVG(
SUM(Review Stars
)
/
COUNT(DISTINCT Review Id
)
) OVER ( ORDER BY Review Date
)
Both measures above work as expected when graphed by day.
However when I switch the graph to week, I receive duplicate weeks
based on this article it appears that the window functions in beast mode aren't compatible with group by options but wanted to post to see if this is still the case.
Any ideas for how to get around this issue? I need the ability to filter by source of review and am hoping I don't have to create a seperate view for each source & an aggregate total.
Best Answer
-
I just got time to try things out a bit, and while at the DATA TABLE level the calculation seems to be working as expected, returning only 5 rows, the visualization is creating additional rows not in the Data Table, might be worth bringing this up to Domo Support.
0
Answers
-
The problem is with the granularity, sadly the Beast Mode function will work with the data the way that comes from the Dataset, in this case daily granularity and will return a different calculation per day. Since these values are different, even when you ask to graph by week you'll get an entry for each day of that week. If you don't need this to be a table, you could try using the OOB Running Total line or Bar Chart chart.
The other thing that comes to mind is handling the granularity via a variable rather than the Graph By option, but that would require more work on what will be displayed, which might not be worth it. Keep in mind your running Avg is Avg the averages, which might not be what you want (your current approach gives the same weight to each week, regardless of the number of reviews received, which might not be ideal (as a rule you should avoid averaging averages or percentages unless you do so in a weighted way))0 -
What are you trying to accomplish with the OVER ( ORDER BY
Review Date
) ? That is what is causing the duplicates.If I solved your problem, please select "yes" above
0 -
@colemenwilson I am using the over() to create the cumulative total
0 -
@marcel_luthi regarding the variable method, do you have any examples or further context you can provide? I'm assuming your variable would be day, month, week, year and that selection would drive the graph axis as well as the order by of the measure
0 -
That is correct @calvert_schaefer, so instead of using the Date field in your graph axis, you'll use a beast mode instead, and then in your rolling calculation in the OVER clause you'll use the same or a slight variation of the beast mode instead. Something along the lines of:
CASE WHEN `granularity` = 'day' THEN `Review Date` WHEN `granularity` = 'week' THEN `Review Date` - INTERVAL (DAYOFWEEK(`Review Date`)-1) DAY WHEN `granularity` = 'month' THEN `Review Date` - INTERVAL (DAY(`Review Date`)-1) DAY END
So depending on the granularity selected you'll get single dates for everything in the same day, week or month (being the beginning of the week (Sunday based) or month), so when you use the order by clause it sees all them as the same date you're sorting on, at least in theory this should work (how that gets display in the chart is a different story and sadly you cannot control the GRAPH BY option via a variable, but you could in theory do a more complex beast mode that translates the date into the format you want to display it as)
0 -
@marcel_luthi thanks for the detail. I have tried this in my model and unfortunately I am getting the same results as previously. I have tried it on a simpler measure of Reviews in the example below.
Date View = CASE
WHENDATE AXIS
='DAY' THENReview Date
WHENDATE AXIS
='WEEK' THENReview Date
- INTERVAL(DAYOFWEEK(Review Date
)-1) DAY
WHENDATE AXIS
='MONTH' THENReview Date
- INTERVAL (DAY(Review Date
)-1) DAY
ENDReviews RT = SUM(COUNT(DISTINCT `Review Id`) )
OVER ( ORDER BY
CASE
WHEN `DATE AXIS`='DAY' THEN `Review Date`
WHEN `DATE AXIS`='WEEK' THEN `Review Date` - INTERVAL(DAYOFWEEK(`Review Date`)-1) DAY
WHEN `DATE AXIS`='MONTH' THEN `Review Date` - INTERVAL (DAY(`Review Date`)-1) DAYEND
When day is selected it works as expected
When week is selected I still get duplicate rows
0 -
So it works so that every entry on each period shows the same value, which the previous approach wouldn't do, but it's still generating one entry per row even if the values are the same. I recall seeing a similar behavior on another question no long ago, but think no solution for that was provided 😓. I might play with something later today but don't have an answer right now.
1 -
I just got time to try things out a bit, and while at the DATA TABLE level the calculation seems to be working as expected, returning only 5 rows, the visualization is creating additional rows not in the Data Table, might be worth bringing this up to Domo Support.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive