Display Top 30, Bottom 30 and Var in a single table

[apacbi_smbc]

I am able to display the top 30 and bottom 30 in a pivot table by using the following:

Dimension -> if(num(aggr(rank(($(vCurrBalance) - $(vPrevWeekDayBalance))/1000000,ID)) <=30 or NUM(AGGR(RANK(($(vCurrBalance) - $(vPrevWeekDayBalance))/1000000,ID)) >= $(vMaxRank)-30, Country)

Columns -> if(num(aggr(rank(($(vCurrBalance) - $(vPrevWeekDayBalance))/1000000,ID)) <=30, 'Top', 'Bottom')

Measure -> sum(aggr($(vCurrBalance) - $(vPrevWeekDayBalance))/1000000

however, I am not able to display the Var.

I tried combining the top 30 and bottom 30 in a straight table as well. However, i am not receiving the expected outcome and not able to include the var as well.

User is expecting to see something like this:

How do i achieve this? To attain the Top 30, Bottom 30 and Var all in a single table.

[snibrahim1993]

Hi @apacbi_smbc  You can add two measures separately for the Top and Bottom then add them in the straight table as well.

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!

[apacbi_smbc]

Hi @snibrahim1993, Already tried. not working.

Top Measure -> if(((num(aggr(rank(($(vCurrentFYBalance) - $(vPrevYTDBalance))/1000000),SUN_ID)) <= 30)),sum(aggr($(vCurrentFYBalance) - $(vPrevYTDBalance),SUN_ID)))/1000000

Bottom Measure ->if(num(aggr(rank($(vCurrentFYBalance) - $(vPrevYTDBalance)),SUN_ID)) >= $(vMaxRank)-30,
sum(aggr($(vCurrentFYBalance) - $(vPrevYTDBalance),SUN_ID)))/1000000

Thing is not all the countries are in both top 30 and bottom 30. the countries vary.

[snibrahim1993]

@apacbi_smbc 

Is the country field the direct field?

Please share the 3 columns expression clearly

[MayilVahanan]

HI

For Bottom, you can try with use minus (-) in the expression

if(num(aggr(rank(-($(vCurrentFYBalance) - $(vPrevYTDBalance))),SUN_ID)) <= 30,
sum(aggr($(vCurrentFYBalance) - $(vPrevYTDBalance),SUN_ID)))/1000000

[apacbi_smbc]

still not working

[apacbi_smbc]

Yes, country is a direct field with expression :

if(num(aggr(rank(($(vCurrentFYBalance) - $(vPrevYTDBalance))/1000000),SUN_ID)) <= 30 or
num(aggr(rank($(vCurrentFYBalance) - $(vPrevYTDBalance)),SUN_ID)) >= $(vMaxRank)-30, Country)

Top -> =if(((num(aggr(rank(($(vCurrentFYBalance) - $(vPrevYTDBalance))/1000000),SUN_ID)) <= 30)),sum(aggr($(vCurrentFYBalance) - $(vPrevYTDBalance),SUN_ID)))/1000000

Bottom -> if(num(aggr(rank($(vCurrentFYBalance) - $(vPrevYTDBalance)),SUN_ID)) >= $(vMaxRank)-30,
sum(aggr($(vCurrentFYBalance) - $(vPrevYTDBalance),SUN_ID)))/1000000

Var -> Top - Bottom

[snibrahim1993]

@apacbi_smbc  Try the below

The modification made in the "Bottom" calculation is the addition of "null()" as the value when the condition is not met. This ensures that if the condition for the "Bottom" calculation is not satisfied, it will display a null value instead of "-".

Bottom=if(num(aggr(rank($(vCurrentFYBalance) - $(vPrevYTDBalance)),SUN_ID)) >= $(vMaxRank)-30, sum(aggr($(vCurrentFYBalance) - $(vPrevYTDBalance),SUN_ID)) / 1000000, null())

[apacbi_smbc]

point is, it is not supposed to be null. there are values when converted into pivot table. but this logic doesn't seem to work in a straight table. it is not allowing me to have both top and bottom in the same table. 

[snibrahim1993]

Can you share the QVF with sample data?