Max Value per Month on a Weekly Feed Dataset

Tatyana_K
Tatyana_K Member
in Magic ETL

We have a dataset that pulls information from SQL that gets updated by the managers once a week for their current value (document review counts and percentage rates for activity per day per user, effecitve rates, price per doc for review).  We are using this for our weekly reporting just fine, but, when we try to set up a card for monthly values, we cannot do that since we would be mutliple reporting numbers. How do we set up a beast mode that will take the values and apply the max rate for the month for each of the calculations noted?

Best Answer

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    So it looks like you want to first identify the latest date entry for each month and then show the metrics that were entered on that day.

     

    In that case, I'm not sure if this is possible in beast mode.  My approach would be to add a field `max_date_for_month` in either mySQL or ETL.

     

    you could then filter with this beast mode:

    case when `Date` = `max_date_for_month` then 'true' else 'false' end

     

    Use that beast mode as a filter to only include the 'true' rows.

     

    Let me know if you prefer etl or mysql and I can help you with the syntax to add the field

     


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

Answers

  • Domo_Diesel
    Domo_Diesel Domo Employee

    Hi @Tatyana_K,

     

    Happy to help.  Just to confirm, you would like each metric to display the max, or highest rate it was at during the month?  In that case, when you use the metric field on a card you will want to set the aggregation to Max.  Or, if using a beast mode you can use the Max() function.  For example, Max(`review count`).  This will give you the max rate for the month, if the card is filtered for one month/the current month.  One other question, is there just one row of data for each metric each week?

     

    Let me know if this is helpful, or if you have any other questions.

     

    Best,

     

    Domo Diesel

  • ST_-Superman-_
    ST_-Superman-_ Coach

    If I am understanding you, you have a table with different metrics for each manager.  You want to know the max for each metric for each month by manager.

     

    I think this would work for that:

     

    MAX(`metric field`) OVER (PARTITION BY `Manager`,`Year`,`Month`)

     

    That should give you the max value for each manager/year/month combination.  


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

    Hi Domo Diesel,

     

    I would love if it was that simple to just use a MAX formula in beast mode. I think this might exceed that or it really is that easy! I'm attaching an example of our underlying data for one project to show how the numbers will increase and how each week has an entry but we only want to report on a monthly basis for the max value (or latest value for that month). Does this help clarify? A weekly chart is great, but monthly doubles up on the reporting metrics as you can see here.

     

    Thanks,

    Tatyana

  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    So it looks like you want to first identify the latest date entry for each month and then show the metrics that were entered on that day.

     

    In that case, I'm not sure if this is possible in beast mode.  My approach would be to add a field `max_date_for_month` in either mySQL or ETL.

     

    you could then filter with this beast mode:

    case when `Date` = `max_date_for_month` then 'true' else 'false' end

     

    Use that beast mode as a filter to only include the 'true' rows.

     

    Let me know if you prefer etl or mysql and I can help you with the syntax to add the field

     


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

    That sounds great! I will try that out. I think I can manage the beast mode after the ETL is set up but if I run into any hurdles, I'll come back to knock on your door @ST_-Superman-_ . Thank you and @Domo_Diesel for your quick assist here. - Tatyana

Categories