Dear All,
I want to implement a calculated item from aggregation of an aggregation. In Tableau I would use LOD function, example:
I want to flag those user with weighted average price over the Role_Bandmax to be 'too_expensive', otherwise just as same as Tier_Role column.
In the table, for TOM, his average price is $174.29, it is over his Role_Bandmax 110, so TOM should be flagged as 'too_expensiv'
For JERRY, his average price is $108,27, it is lower than his Role_Bandmax, 130, so JERRY should be flagged as same as his Tier_Role, that is 'tier2'
Transaction_ID | User_ID | User_Role | Role_Bandmax | Tier_Role | price | amount | weighted avg price | new_Flag |
1 | TOM | developer | 110 | tier1 | 100 | 1 | 174,2857143 | too_expensive |
2 | TOM | developer | 110 | tier1 | 90 | 2 | 174,2857143 | too_expensive |
3 | TOM | developer | 110 | tier1 | 120 | 3 | 174,2857143 | too_expensive |
4 | TOM | developer | 110 | tier1 | 300 | 2 | 174,2857143 | too_expensive |
5 | TOM | developer | 110 | tier1 | 200 | 5 | 174,2857143 | too_expensive |
6 | TOM | developer | 110 | tier1 | 200 | 1 | 174,2857143 | too_expensive |
7 | JERRY | Admin | 130 | tier2 | 110 | 3 | 108,2666667 | tier2 |
8 | JERRY | Admin | 130 | tier2 | 120 | 4 | 108,2666667 | tier2 |
9 | JERRY | Admin | 130 | tier2 | 140 | 5 | 108,2666667 | tier2 |
10 | JERRY | Admin | 130 | tier2 | 120 | 6 | 108,2666667 | tier2 |
11 | JERRY | Admin | 130 | tier2 | 110 | 7 | 108,2666667 | tier2 |
12 | JERRY | Admin | 130 | tier2 | 130 | 8 | 108,2666667 | tier2 |
13 | JERRY | Admin | 130 | tier2 | 110 | 9 | 108,2666667 | tier2 |
14 | JERRY | Admin | 130 | tier2 | 90 | 10 | 108,2666667 | tier2 |
15 | JERRY | Admin | 130 | tier2 | 90 | 11 | 108,2666667 | tier2 |
16 | JERRY | Admin | 130 | tier2 | 100 | 12 | 108,2666667 | tier2 |
Thanks in advance!
This is posted to the SAS Visual Analytics community but it does not seem like a VA issue. Which SAS product are you using? For example, SAS Studio?
-------------------------------------------------------------------------
Four tips to remember when you contact SAS Technical Support
Creating Beautiful Reports
SAS Visual Analytics Learning Center
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.