Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have requirement
Sorting for Others
Requirement is to show the top 20 Entity and group the balance debtors under Others and display Others portion in the last row of the Pivot. We are using rank to create another category called Others. However we are unable to display Others in the last row
Please amend as required
Thanks in advance
Regards,
Puneet Agarwal
In Classification Part of Object Select the "Sort by Expression" Flag
put the expression that passed just adapting the return
if( aggr( rank(sum({$<MONTH={"$(vCurrentMonth)"}>} Sales)),
Entity) <=20,sum({$<MONTH={"$(vCurrentMonth)"}>} Sales),-1)
Would you be able to share a sample where we can see this issue and test it out?
Hi
You can use the same measurement formula to create the sort
=AVG(
Aggr(
if(
rank(sum(Value),4)<=20,sum(Value),-1
)
,Dimension)
)
regards
Sunny,
Sharing a QVF may not be possible..
@Yoshidaqlik
My calucated dimension looks someting like this : if( aggr( rank(sum({$<MONTH={"$(vCurrentMonth)"}>} Sales)),
Entity) <=20,Entity,'Others')
Can you please suggest/ provide so that i can short starting 19 entity in desc order and others at bottom.
TIA
Regards,
Puneet Agarwal
In Classification Part of Object Select the "Sort by Expression" Flag
put the expression that passed just adapting the return
if( aggr( rank(sum({$<MONTH={"$(vCurrentMonth)"}>} Sales)),
Entity) <=20,sum({$<MONTH={"$(vCurrentMonth)"}>} Sales),-1)
Hi Yoshida,
Thanks a ton i got the solution. but unfortunately couldn't understand the expression,if you have time can you please elaborate the expression (explain).
Regards,
Puneet Agarwal
Hi Yoshida
I have posted one more query, can you please look into it. Below is the link
https://community.qlik.com/t5/New-to-Qlik-Sense/Measure-in-x-axis-and-dimension-as-y-axis/m-p/161642...
Regards
Puneet Agarwal
if( aggr( rank(sum({$<MONTH={"$(vCurrentMonth)"}>} Sales)), Entity) <=20,sum({$<MONTH={"$(vCurrentMonth)"}>} Sales),-1)
clear
explaining about the function
the rank function will bring the sum positioning
Aggr function assembles a virtual table over the Entity dimension
it's like you have a table with an Entity column and a measurement column that would be the sum
After this table is assembled a test is made if the rank is less than or equal to 20, if the test is true it will bring the sum result to rank otherwise it will bring the number -1 leaving the Other position last
Hi Yoshida,
Thanks a ton for the clear explanation.
Regards,
Puneet Agarwal