Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
ujjwalraja
Contributor III
Contributor III

QlikView QlikSense Scenario for Complex Reporting

++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

ujjwalraja_0-1658079461938.png

 

, When I select a particular Broker the results are coming correct but again it is only for single Broker.

ujjwalraja_1-1658079501776.png

 

Please help me on how to achieve this scenario as per the requirement table.

 

Labels (1)
2 Replies
edwin
Master
Master

edwin
Master
Master

you just look for rank =1, 2, or 3