Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
++Updated to include all the scenarios
Hi Qlik Community,
I have input data as
T1:
LOAD * INLINE [
Broker, Carrier, Sales
B, BC1, 10
B, BC2, 20
B, BC3, 30
B, BC4, 20
B, BC4, 20
D, DC1, 80
D, DC2, 90
D, DC3, 60
D, DC3, 40
];
The Requirement is to get a report built in the below format
| Broker | Top 1st Carrier | Top 2nd Carrier | Top 1st Carrier Sales | Top 2nd Carrier Sales | Top 1st Carrier %age | Top 2nd Carrier %age |
| B | BC4 | BC3 | 40 | 30 | 20+20/20+20+30+20+10 | 30/20+20+30+20+10 |
| D | DC3 | DC2 | 100 | 90 | 60+40/80+90+60+40 | 90/80+90+60+40 |
I started building it in below approach-
Dimension- Broker
Measures-
Top 1st Carrier- FirstSortedValue(Carrier,-Aggr(Sum(Sales),Broker,Carrier),1)
Top 2nd Carrier - FirstSortedValue(Carrier,-Aggr(Sum(Sales),Broker,Carrier),2)
Top 1st Carrier Sales - Sum({<Carrier={'$(=FirstSortedValue(Carrier,-Aggr(Sum(Sales),Broker,Carrier),1))'}>}Sales)
Top 2nd Carrier Sales- Sum({<Carrier={'$(=FirstSortedValue(Carrier,-Aggr(Sum(Sales),Broker,Carrier),2))'}>}Sales)
The result are coming correct for the first 2 measures but for the next 2 the results are not coming correct
, When I select a particular Broker the results are coming correct but again it is only for single Broker.
Please help me on how to achieve this scenario as per the requirement table.
