How to show a specific date with an aggregate (max)

jordan_platinum
jordan_platinum Member
in Charting

Hey guys,

So I'm creating a card that looks at the sales of our employees each day and then lists the day with the max sales.

Our Data is structured by transaction, so I had to create a Magic ETL to Group the sales by date and employee in order to find the max by day. The problem I'm having is when I try and display the date associated with that max sales day.

Because the max sales is an aggregate function, the information requires me to put an aggregate for the date.

If I don't do that it shows me all of the sales for the time period I'm looking at and thus shows me multiple dates per employee when I only want to see their best day.


As an example this is what I want:

Employee | Sales | Date

DC $11,000 7/11/22

SL $9,200 7/8/22

DA $8,456 7/6/22

PA $7,467 7/8/22

...


What I'm getting looks more like this:

Employee | Sales | Date

DC $11,000 7/11/22

DC (same) $10,856 7/7/22

SL $9,200 7/8/22

DA $8,456 7/6/22

SL $8,265 7/5/22

PA $7,467 7/8/22


The closest I've gotten to getting it to work is using a pivot table but that shows all the dates they've had sales, where I only want one entry per employee so that we have a quick list to glance over. Any suggestions on how to add the date without increasing the data my table shows?

Tagged:

Best Answer

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

    Once you have your sales grouped by Employee and Date, you could also use a rank and window function.

    You would want to rank the Sales $s in descending order and partition the data by Employee.

    Then, in your visualization, if you only wanted to show the best day for each employee, you would filter the data to only include values where "rank" = 1


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

Answers

  • GrantSmith
    GrantSmith Coach

    In your Magic ETL - Group by the Employee, get the Max Sales, join that to your original dataset based on employee and sales amount. That will get you the date without having to use the aggregate on the date field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ST_-Superman-_
    ST_-Superman-_ Coach
    Answer ✓

    Once you have your sales grouped by Employee and Date, you could also use a rank and window function.

    You would want to rank the Sales $s in descending order and partition the data by Employee.

    Then, in your visualization, if you only wanted to show the best day for each employee, you would filter the data to only include values where "rank" = 1


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

    Thanks guys for your replies I appreciate the help.

    @Grant this would work, but it greatly increases my row total which I don't want to do in this particular case.

    @ST_Superman I am implementing this now and it appears to be working.

Categories