Calculate average time to accumulate to a value.

Reply
Highlighted
White Belt

Calculate average time to accumulate to a value.

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.

 

Tags (1)
Highlighted
Black Belt

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.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Black Belt

@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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.