Plot of rolling calculation

Reply
Highlighted
Visitor

Plot of rolling calculation

I'm fresh into Domo and but have reached my limit with a rather interesting request.

 

I have created a card that ranks the top 60 performing days out of the previous 120 days.

However, it only does this for the current day.  In the attached graph the number I'm interested in is the 388.  That's "Today's number"

 

For any day I would like to take the minimum of the top 60 days (out of previous 120) and then plot that value over time.

So for a given date Domo would calculate the top 60 best performing days out of the previous 120.  Then take the _minimum_ best forming day (ie the lowest value in the top 60) and the score of that day that would become the score for the original day of the calculation.

Any hints?

 

In a related question - I think if I can see the code for the the current chart that will be a big step towards creating the code that I want. (Ie I sometimes work better in code than GUI)  Is there any way to see the code that was generated for a card?  Then I can use that as a starting point.

 

Thanks in advance!


Accepted Solutions
Black Belt

Re: Plot of rolling calculation

I do not believe that this can be accomplished via the calculated fields, or beast modes.

 

If I'm understanding the request, you want to get a rank of the top 60 days of sales performance (out of the last 120 days) for each day, and then return the value of the 60th best day out of those 120.  

 

As your post showed, that means that for today's date you would want to see the value $388.  Does the date of that value matter to you as well?

 

You are essentially wanting to track how the average sales performance is trending.  You are calculating the median instead of the mean.  For example, if a store had 60 great sales days and 60 horrible sales days, then then "rank 60" value would look really good even though overall sales could be down.

 

I might suggest calculating a rolling "120 day avg" sales number.  

 

Either way, you will need to make these calculations from inside a dataflow and not via a beast mode.

 

Are you more comfortable with the drag and drop ETL, or the mySQL data flows?


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

All Replies
Black Belt

Re: Plot of rolling calculation

I do not believe that this can be accomplished via the calculated fields, or beast modes.

 

If I'm understanding the request, you want to get a rank of the top 60 days of sales performance (out of the last 120 days) for each day, and then return the value of the 60th best day out of those 120.  

 

As your post showed, that means that for today's date you would want to see the value $388.  Does the date of that value matter to you as well?

 

You are essentially wanting to track how the average sales performance is trending.  You are calculating the median instead of the mean.  For example, if a store had 60 great sales days and 60 horrible sales days, then then "rank 60" value would look really good even though overall sales could be down.

 

I might suggest calculating a rolling "120 day avg" sales number.  

 

Either way, you will need to make these calculations from inside a dataflow and not via a beast mode.

 

Are you more comfortable with the drag and drop ETL, or the mySQL data flows?


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Visitor

Re: Plot of rolling calculation

Thanks for that response!

I think you have a pretty good understanding of what I need.  No the date of the lowest performance doesn't matter.

 

Unfortunately I'm mainly interested in simply making sure that minimum stays above a certain threshold.  The average of the top group is not relevant.

 

I will start to poke around in the dataflow model and go from there.

 

Much appreciated!

Announcements
We want your feedback to improve the Dojo Community! If you are coming to the Dojo Award Ceremony next week Click here to learn more!