Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ozcano
Contributor III
Contributor III

Finding Maximum measure value in a pivot table row

 

Hello

I have a pivot table as in the in the following picture. It shows the number of items (inventory) in each OrdererStoreCode (branch). What I want to do is find the OrderstoreCode which has the maximum of inventory and than change color of cell to let's say yellow. For example, for ItemCode Samsung, I want to find out that Orderstorecode3 has the maxmium of 12 items compared to other stores and show value 12 as color code yellow. . How can I do that?

ozcano_0-1707132724056.png

 

 

Labels (3)
1 Solution

Accepted Solutions
Bruddah_IZ
Partner - Contributor II
Partner - Contributor II

Hi @ozcano ,

If you use Rank() function as suggested by @LRuCelver and use it as the background expression for the only metric in your pivot table, then you can get the desired result:

2024-02-05_15-47-21.png

Here is the formula to get to the result above:  

Pick(Aggr(Rank(Sum(Inventory)), ItemCode, Branch)
  , Yellow()    // 1st
  , LightBlue() // 2nd
  , LightGray() // 3rd
)

Please let me know if this is exactly what you have been looking for. Otherwise, please clarify what you are struggling with

Good luck,
ilya

 

View solution in original post

4 Replies
LRuCelver
Partner - Creator
Partner - Creator

You can use the Rank() function to identify the max value in a column.

ozcano
Contributor III
Contributor III
Author

Would you be able to give more detail plase?

Bruddah_IZ
Partner - Contributor II
Partner - Contributor II

Hi @ozcano ,

If you use Rank() function as suggested by @LRuCelver and use it as the background expression for the only metric in your pivot table, then you can get the desired result:

2024-02-05_15-47-21.png

Here is the formula to get to the result above:  

Pick(Aggr(Rank(Sum(Inventory)), ItemCode, Branch)
  , Yellow()    // 1st
  , LightBlue() // 2nd
  , LightGray() // 3rd
)

Please let me know if this is exactly what you have been looking for. Otherwise, please clarify what you are struggling with

Good luck,
ilya

 

ozcano
Contributor III
Contributor III
Author

Hello,

This solved the problem. But I have another one. Since Pivot table made up multiple dimensions in terms of rows, I have dimensions ProductClass, ProductFirm in addtion to ItemCode. I want to rank that these rows too. Is It possible? I tried this, but did not work:

Pick(Aggr(Rank(Sum( Inventory)), (ItemCode,ProductClass, ProductFirm), Branch) , RGB(0, 44, 0) , RGB(0, 130, 0) ,RGB(0, 180, 0),RGB(0, 220, 0))

Regards

ozcano_3-1707209066183.png