topic Re: how to show this expression? in QlikView

how to show this expression?

rcandeo — Thu, 25 Aug 2016 11:12:26 GMT

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?

Anonymous — Thu, 25 Aug 2016 11:17:52 GMT

Use Rank function

Re: how to show this expression?

sunny_talwar — Thu, 25 Aug 2016 11:18:28 GMT

How about this:

FirstSortedValue(Country, -Aggr(

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

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

Country))

Re: how to show this expression?

agomes1971 — Thu, 25 Aug 2016 11:19:29 GMT

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?

antoniotiman — Thu, 25 Aug 2016 11:20:27 GMT

Hi,

try

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

Re: how to show this expression?

Gysbert_Wassenaar — Thu, 25 Aug 2016 11:26:57 GMT

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?

rcandeo — Thu, 25 Aug 2016 12:30:52 GMT

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?

Gysbert_Wassenaar — Thu, 25 Aug 2016 12:33:49 GMT

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?

rcandeo — Thu, 25 Aug 2016 12:48:30 GMT

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

Re: how to show this expression?

rcandeo — Thu, 25 Aug 2016 12:48:45 GMT

Thank you very much

Re: how to show this expression?

sunny_talwar — Thu, 25 Aug 2016 12:50:47 GMT

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%')