Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I'm new to Qlik Sense and have been working on a project for weeks. I'm having trouble showing only the top 10 brands in a straight table based on profit for FY23.
Please help
Have tried below and not working as expected
1. if(Rank(aggr(Sum({<period={'FY23'}>}Profit),Name))<=10,Name,null())
2. sum({<Name={"=rank(sum (Profit))<=10"},period={'FY23'}>}profit)
Data in Straight table as shown below and Calculated measures are highlighted in Red
Name | Type | Sales FY23 |
Cost FY23 |
Profit FY23 |
Sales FY22 |
Cost FY22 |
Profit FY22 |
|
Saturn | N | 226710 | 68013 | 158697 | 722517 | 144503 | 578014 | |
GMC | D | 462284 | 92457 | 369827 | 324301 | 64860 | 259441 | |
Honda | A | 408589 | 122577 | 286012 | 609902 | 60990 | 548912 | |
Mercury | N | 288338 | 28834 | 259504 | 437055 | 87411 | 349644 | |
Lexus | A | 722517 | 144503 | 578014 | 250148 | 75044 | 175104 | |
Lincoln | A | 324301 | 64860 | 259441 | 208856 | 41771 | 167085 | |
Aston Martin | S | 609902 | 60990 | 548912 | 749215 | 149843 | 599372 | |
Volkswagen | N | 437055 | 87411 | 349644 | 279537 | 83861 | 195676 | |
Pontiac | N | 250148 | 75044 | 175104 | 268097 | 26810 | 241287 | |
Ford | S | 208856 | 41771 | 167085 | 603812 | 120762 | 483050 | |
Nissan | D | 749215 | 149843 | 599372 | 613526 | 184058 | 429468 | |
Chevrolet | D | 279537 | 83861 | 195676 | 234414 | 70324 | 164090 | |
Porsche | S | 268097 | 26810 | 241287 | 537179 | 53718 | 483461 | |
Dodge | N | 603812 | 120762 | 483050 | 269429 | 80829 | 188600 | |
Mitsubishi | D | 613526 | 184058 | 429468 | 558831 | 167649 | 391182 | |
BMW | S | 234414 | 70324 | 164090 | 455060 | 91012 | 364048 | |
Oldsmobile | S | 537179 | 53718 | 483461 | 406935 | 40694 | 366242 | |
Acura | D | 269429 | 80829 | 188600 | 656743 | 131349 | 525394 | |
Subaru | D | 592722 | 177817 | 414905 | 268842 | 80653 | 188189 | |
Mercedes-Benz | S | 546320 | 109264 | 437056 | 272302 | 81691 | 190611 | |
Audi | D | 539708 | 53971 | 485737 | 267269 | 80181 | 187088 | |
Hyundai | S | 558831 | 167649 | 391182 | 533553 | 160066 | 373487 | |
Isuzu | S | 455060 | 91012 | 364048 | 209997 | 41999 | 167998 | |
Lotus | N | 406935 | 40694 | 366242 | 577426 | 57743 | 519683 |
Desired Output as below
Name | Type | Sales FY23 |
Cost FY23 |
Profit FY23 |
Sales FY22 |
Cost FY22 |
Profit FY22 |
|
Nissan | D | 749215 | 149843 | 599372 | 613526 | 184058 | 429468 | |
Lexus | A | 722517 | 144503 | 578014 | 250148 | 75044 | 175104 | |
Aston Martin | S | 609902 | 60990 | 548912 | 749215 | 149843 | 599372 | |
Audi | D | 539708 | 53971 | 485737 | 267269 | 80181 | 187088 | |
Oldsmobile | S | 537179 | 53718 | 483461 | 406935 | 40694 | 366242 | |
Dodge | N | 603812 | 120762 | 483050 | 269429 | 80829 | 188600 | |
Mercedes-Benz | S | 546320 | 109264 | 437056 | 272302 | 81691 | 190611 | |
Mitsubishi | D | 613526 | 184058 | 429468 | 558831 | 167649 | 391182 | |
Subaru | D | 592722 | 177817 | 414905 | 268842 | 80653 | 188189 | |
Hyundai | S | 558831 | 167649 | 391182 | 533553 | 160066 | 373487 |
@jagdishbr
By applying the filter outside, you leave it as priority, see below:
To Measure:
={<period={'FY23'}>}sum({<Name={"=rank(sum (Profit),4)<=10"}>}Profit)
for dimension, disable nulls:
={<period={'FY23'}>}aggr(only({< Name ={"=Rank(sum(Profit),4)<=10"}>}Name),Name)
- Matheus
@jagdishbr
By applying the filter outside, you leave it as priority, see below:
To Measure:
={<period={'FY23'}>}sum({<Name={"=rank(sum (Profit),4)<=10"}>}Profit)
for dimension, disable nulls:
={<period={'FY23'}>}aggr(only({< Name ={"=Rank(sum(Profit),4)<=10"}>}Name),Name)
- Matheus
Thanks a lot Matheus for your time and effort in addressing the issue.