Cumulative Total Measures

calvert_schaefer

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.

marcel_luthi

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.

marcel_luthi

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))

ColemenWilson

What are you trying to accomplish with the OVER ( ORDER BY Review Date ) ? That is what is causing the duplicates.

calvert_schaefer

@colemenwilson I am using the over() to create the cumulative total

calvert_schaefer

@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

marcel_luthi

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)

calvert_schaefer

@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
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) DAY
END

Reviews 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) DAY

END

When day is selected it works as expected

When week is selected I still get duplicate rows

marcel_luthi

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.

marcel_luthi

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.