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

[Navars]

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

[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]

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 · Accepted solution]

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)

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)