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.

 

Tagged:

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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
    MarkSnodgrass Portland, Oregon 🔵

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

  • 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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!