Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Navars
Creator
Creator

Top 5 and bottom 5 results in a Bar chart after calculating the variance

Hi All,

Firstly i am calculating the variance (Current month - previous Month) for the Model(Dim)

exp is below for variance.

sum({<mnt1={"$(=vMax)"}>}USD_MATERIAL_COST) - sum({<mnt={"$(=vPrv)"}>}USD_MATERIAL_COST)

Now, i want to get the Top5 and bottom 5 Model based on the variance value in single Bar chart.

only passing the Model as dim.

Attaching some sample data qvd.

 Thanks In Advance!!

@sunny_talwar

 

Labels (5)
1 Solution

Accepted Solutions
rubenmarin

Hi, I don't have the mnt field or the variables, but this calculated dimension works:

=Aggr(If(Rank(sum(USD_MATERIAL_COST))<=5 or Rank(-sum(USD_MATERIAL_COST))<=5,MODEL) ,MODEL)

rubenmarin_0-1646809883624.png

So maybe it's something with the variance

Now I see that it will need another extra parenthesys for bottom:

=Aggr(If(Rank(sum({<mnt1={"$(=vMax)"}>}USD_MATERIAL_COST) - sum({<mnt={"$(=vPrv)"}>}USD_MATERIAL_COST))<=5 or Rank(-(sum({<mnt1={"$(=vMax)"}>}USD_MATERIAL_COST) - sum({<mnt={"$(=vPrv)"}>}USD_MATERIAL_COST)))<=5,MODEL) ,MODEL)

View solution in original post

3 Replies
rubenmarin

Hi, you can try with a calculated dimension like:

=Aggr(If(Rank(sum({<mnt1={"$(=vMax)"}>}USD_MATERIAL_COST) - sum({<mnt={"$(=vPrv)"}>}USD_MATERIAL_COST))<=5 or Rank(-sum({<mnt1={"$(=vMax)"}>}USD_MATERIAL_COST) - sum({<mnt={"$(=vPrv)"}>}USD_MATERIAL_COST))<=5,MODEL) ,MODEL)

And uncheck the option to show null values on dimension.

Navars
Creator
Creator
Author

Hi Rubenmarin,

Thanks for your response.

I tried your expression in calculated dimension but i am getting only top 5 variances, not getting the bottom 5 variances.

rubenmarin

Hi, I don't have the mnt field or the variables, but this calculated dimension works:

=Aggr(If(Rank(sum(USD_MATERIAL_COST))<=5 or Rank(-sum(USD_MATERIAL_COST))<=5,MODEL) ,MODEL)

rubenmarin_0-1646809883624.png

So maybe it's something with the variance

Now I see that it will need another extra parenthesys for bottom:

=Aggr(If(Rank(sum({<mnt1={"$(=vMax)"}>}USD_MATERIAL_COST) - sum({<mnt={"$(=vPrv)"}>}USD_MATERIAL_COST))<=5 or Rank(-(sum({<mnt1={"$(=vMax)"}>}USD_MATERIAL_COST) - sum({<mnt={"$(=vPrv)"}>}USD_MATERIAL_COST)))<=5,MODEL) ,MODEL)