Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jagdishbr
Contributor III
Contributor III

Need help bringng Rank into Straight table

Hi,

I'm new to qlikview and have requirement to bring rank into straight table and below are available columns

the requirement is to calculate rank by considering only 3 column Region, Product and Period.

Client IDRegionProductPeriodSalesRank
3928APACCRFY186131 
4888APACCRFY185083 
1026APACCRFY183524 
3928APACCR1H196491 
4888APACCR1H194661 
1026APACCR1H199078 
3928AmericasCRFY183743 
4888AmericasCRFY187832 
1026AmericasCRFY187469 
3928AmericasCR1H198104 
4888AmericasCR1H198620 
1026AmericasCR1H196767 
3928APACARFY182178 
4888APACARFY181827 
1026APACARFY187328 
3928APACAR1H196117 
4888APACAR1H196399 
1026APACAR1H193358 
3928AmericasARFY184105 
4888AmericasARFY187813 
1026AmericasARFY186788 
3928AmericasAR1H197567 
4888AmericasAR1H194463 
1026AmericasAR1H195986 

 

Please help me.

 

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

I had a typo in my expression above.... try this

Aggr(Rank(Sum(Sales)), Region, Product, Period, [Client ID])

image.png 

View solution in original post

7 Replies
sunny_talwar

What is the expected output for the rank column based on the sample data shared?

sunny_talwar

May be this

Aggr(Rank(Sum(Sales), Region, Product, Period, [Client ID])

This will give you rank for each Client ID within a single Region, Product, Period. Rank will reset to 1 as any of the three dimension value changes.

jagdishbr
Contributor III
Contributor III
Author

Thanks for your response sunny.

If we include client Id as a parameter, rank will get populated for client for each products.

The requirement is to get top 25 clients for each products by region by period

If I'm not clear, I will share the table agin populating rank.

jagdishbr
Contributor III
Contributor III
Author

This is the output expected.

Client IDRegionProductPeriodSalesRank
3928APACCRFY1861311
4888APACCRFY1850832
1026APACCRFY1835243
3928APACCR1H1964912
4888APACCR1H1946613
1026APACCR1H1990781
3928AmericasCRFY1837433
4888AmericasCRFY1878321
1026AmericasCRFY1874692
3928AmericasCR1H1981042
4888AmericasCR1H1986201
1026AmericasCR1H1967673
3928APACARFY1821782
4888APACARFY1818273
1026APACARFY1873281
3928APACAR1H1961172
4888APACAR1H1963991
1026APACAR1H1933583
3928AmericasARFY1841053
4888AmericasARFY1878131
1026AmericasARFY1867882
3928AmericasAR1H1975671
4888AmericasAR1H1944632
1026AmericasAR1H1959862
sunny_talwar

I had a typo in my expression above.... try this

Aggr(Rank(Sum(Sales)), Region, Product, Period, [Client ID])

image.png 

jagdishbr
Contributor III
Contributor III
Author

Thanks for your help sunny,

I could see the output as required. however, there are clients without sales or as sales as 0 for those client rank started appearing with in the range like 1213-1339. in ran column is there a way to fix this?

it will be better Rank continues as 1213, 1214,1215 till 1339 or appear 1213 rest of clients whose sales = 0

 

jagdishbr
Contributor III
Contributor III
Author

I figured out the solution by enhancing your function by value to mode and fmt.

 

Aggr(Rank(Sum(Sales), 4, 1), Region, Product, Period, [Client ID])