10 Replies Latest reply: Aug 25, 2016 8:50 AM by Sunny Talwar

# how to show this expression?

I have to put a KPI inside a text box showing the country that has the most growth comparing actual year x prior year.

I use this expression below in a chart straight table and it works using a dimension limits for 1 largest value.

(sum({<[Year] = {'\$(vCurrentYear)'}>} amount) - sum({<[Year] = {'\$(vPriorYear)'}>} amount))

/

sum({<[Year] = {'\$(vPriorYear)'}>} amount)

But how to put this expression showing the Country as below without having the dimension limits for largest valeu, as this is not a chart table anymore?

China - 23.42%

• ###### Re: how to show this expression?

Hi

Use Rank function

• ###### Re: how to show this expression?

FirstSortedValue(Country, -Aggr(

(sum({<[Year] = {'\$(vCurrentYear)'}>} amount) - sum({<[Year] = {'\$(vPriorYear)'}>} amount))

/

sum({<[Year] = {'\$(vPriorYear)'}>} amount),

Country))

• ###### Re: how to show this expression?

This gave the country, but I am not being able to show the number beside, can you help me again?

• ###### Re: how to show this expression?

use max instead of firstsortedvalue and remove the first argument and the comma of course and the minus ( - ) character.

• ###### Re: how to show this expression?

Just like Gysbert mentioned, try this to get the max Value

Num(Max(Aggr(

(sum({<[Year] = {'\$(vCurrentYear)'}>} amount) - sum({<[Year] = {'\$(vPriorYear)'}>} amount))

/

sum({<[Year] = {'\$(vPriorYear)'}>} amount),

Country)), '##.00%')

• ###### Re: how to show this expression?

Hi,

try

= yourcountryfield & ' - ' &

num((sum({<[Year] = {'\$(vCurrentYear)'}>} amount) - sum({<[Year] = {'\$(vPriorYear)'}>} amount))

/

sum({<[Year] = {'\$(vPriorYear)'}>} amount), #.##0,00%)

HTH

André Gomes

• ###### Re: how to show this expression?

Hi,

try

FirstSortedValue(DISTINCT Country,-Aggr(YourExpression,Country))

• ###### Re: how to show this expression?

Perhaps like this:

vExpr1: sum({<[Year] = {'\$(vCurrentYear)'}, >} amount)/sum({<[Year] = {'\$(vPriorYear)'}>} amount) - 1

=Only({<Country={"=rank(\$(vExpr1))=1"}>}Country) & ' - ' &

num( sum({<[Year] = {'\$(vCurrentYear)'}, Country={"=rank(\$(vExpr1))=1"}>} amount)/sum({<[Year] = {'\$(vPriorYear)'}, Country={"=rank(\$(vExpr1))=1"}>} amount) - 1 ,'0.00%')

• ###### Re: how to show this expression?

I had a null value in this expression, but thank you for all

• ###### Re: how to show this expression?

Thank you very much