How to show a specific date with an aggregate (max)
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?
Best 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
3
Answers
-
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!**1 -
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
3 -
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.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive