Calculate average time to accumulate to a value.

user038617

Consider a dataset like this:

 

Person | Topic | Points | Date

--------- | ------- | -------- | ------ 

John    |  Foo  | 80       | 2020-09-10

John    |  Foo  | 40       | 2020-09-12

John    |  Bar  | 63       | 2020-09-14

Sally    |  Bar  | 60       | 2020-09-16

Sally    |  Foo  | 90       | 2020-09-16

Sally    |  Foo  | 110     | 2020-09-18

 

Assuming the dataset is much larger with more people topics, points and dates, I'd like to be able to plot a vertical box graph of duration (y-axis) for each person to accumulate X (e.g. 100) points for each topic (x-axis). So for each box plot (for each topic), I should see a spread of durations it took for each person to accumulate X points.

 

I'd ideally like to do this with Beast Mode so I can adjust timeframes meaning I can't build point accumulation into a dataflow.

 

jaeW_at_Onyx

ooof this was fun little challenge!   https://www.youtube.com/watch?v=eifSYZIcPzg the short answer 1) you can't do it 100% as beast modes in Domo (you can get 75% there with window functions but it's not close enough). 2) you should probably do this in magic 2.0 (beta) it'll be super fast if your data isn't streaming and a reasonable size.  let's say 50M rows or less. 3) if you have adrenaline dataflows use that 4)  a)  implement your beast modes for cumulative sum, and date of first transaction per user as calculated columns in a dataset views (beta) b) then materialize the dataset using dataset copy c) implement a rank (where you select the first record per person that crosses the threshold value (100 points) as a cumulative sum with a nested CASE statement d) visualize the data while filtering where rank = 1 e) drink a beer b/c this is way more work than magic.

MarkSnodgrass

@jaeW_at_Onyx is correct that you won't be able to do this in a beast mode, but you should be able to do this with the Rank & Window tile in Magic ETL utilizing the SUM and LAG functions. Here's a link to the KB article: https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

You would utilize the SUM function to sum your points column. This would give you a running total of their points after each date. You could then utilize the LAG function to get the previous date next to the next date for that person. You can then use the DATEDIFF function to get the number of days between records. 

In Analyzer, you could then create a beast mode that would allow you to control the number of points you are looking for. 

Hope this helps.

Alex_Florea

Hi Jae, your hack into the Data view sum(sum()) seems so poweful. But when I tried it I get: Invalid query, check your DataSet schema". And this is after the changes Domo implemented into their system a few days ago (new features)

This formula is valid:

sum(sum(`Sum Recovered $`)) over()

But when I save and close, this error appears:

Invalid query, check your DataSet schema

Why? I need a SUM() over() to get the total of the column into each cell. Please advise, thank you.

jaeW_at_Onyx

i'd start by confirming that

sum(sum(1)) over () 

yields the expected results. from there it's probably an issue with your column spelling. alternatively, try using a column that doesn't have a $. that is a special character and probably should be used in a column name for this exact reason.