How to graph/program difference from average without hardcoding time frame?

AndrewMeier
AndrewMeier
in Magic ETL

We have a dataset that has 1 survey per row. Our ultimate end target is to show difference from average score by employee for varying time frames.

 

For example if the survey score overall average was 85 and the average of John Smith's surveys was 88 we want to show +3.

 

This can easily be programmed in and hard coded in ETLs if there is a set time frame you want to calculate the average over. However, ideally we would like the time frame to be able to be set/picked by the end user of the dashboard. For example to be able to see difference from average Last Week or Last Month or YTD based on the user's selection. This is where we are getting stuck. At the beast mode/card level i am not sure how to both calculate the Overall Average while also segmenting it by Rep so that we can calculate the Individual's Average. Any ideas on how to handle this?

 

I hope that explanation of what we're trying to do makes sense. Thanks!

 

 

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Super easy!

    Just UNION the data onto itself.  And then replace the rep's name in the second copy with "all reps" then use a Window Function in Analyzer

     

    sum(score) /

    sum(sum(case when rep = 'all reps' then score end )) over ()

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories