topic Re: Display Top 30, Bottom 30 and Var in a single table in App Development

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

apacbi_smbc — Wed, 31 May 2023 01:05:58 GMT

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.

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

snibrahim1993 — Wed, 31 May 2023 08:51:31 GMT

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!

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

apacbi_smbc — Wed, 31 May 2023 09:37:16 GMT

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.

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

snibrahim1993 — Wed, 31 May 2023 09:50:58 GMT

@apacbi_smbc

Is the country field the direct field?

Please share the 3 columns expression clearly

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

MayilVahanan — Wed, 31 May 2023 09:53:06 GMT

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

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

apacbi_smbc — Wed, 31 May 2023 09:56:28 GMT

still not working

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

apacbi_smbc — Wed, 31 May 2023 09:59:33 GMT

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

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

snibrahim1993 — Wed, 31 May 2023 10:19:25 GMT

@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())

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

apacbi_smbc — Thu, 01 Jun 2023 01:09:49 GMT

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.

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

snibrahim1993 — Thu, 01 Jun 2023 01:11:17 GMT

Can you share the QVF with sample data?