topic showing max in text box in QlikView

showing max in text box

Sat, 22 Oct 2011 12:07:34 GMT

I have a set anaylsis in straight table which is sorted bu max price/cost with product dimension I would like to show max price/cost product in text box.

Re: showing max in text box

Miguel_Angel_Baeyens — Sat, 22 Oct 2011 12:29:58 GMT

Hi,

The following expression should do

=Num(
Max({1} TOTAL Aggr(Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} UnitPrice)
/
Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} TotalProductCost), Product))
, '#,##0%')

The "{1} TOTAL" part is needed because of the locked value in YearMonth field. Using instead

=Num(
Max({< [$Year Month] = >} Aggr(Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} UnitPrice)
/
Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} TotalProductCost), Product))
, '#,##0%')

should work as well

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

showing max in text box

Sat, 22 Oct 2011 12:49:30 GMT

how can I show product name on text box

Re: showing max in text box

Miguel_Angel_Baeyens — Sat, 22 Oct 2011 16:25:27 GMT

Hi,

This should do

=Only({1} If(
Aggr(
Rank( 
Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} UnitPrice) / Sum({1< Employee = {'Jae Pak', 'Tsvi Reiter'} >} TotalProductCost), 4, 1)
, Product) = 1
, Product))

Kind of cumbersome because of the selection, that you want to ignore, hence all those {1}.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

showing max in text box

Sat, 22 Oct 2011 17:40:22 GMT

you are perfect Miguel thank you

last question::))) is there any special meaning of 4

Re: showing max in text box

Miguel_Angel_Baeyens — Sat, 22 Oct 2011 17:46:00 GMT

Hi,

I'm glad to help. that ", 4, 1" refers to the ranking function, that allows several parameters. Actually that will work if you remove that part, but I'm used to specify it, even when there is no need.

Rank(Something, 4, 1)

Where "Something" is an expression reads "return the rank of something, giving each row a value, and in case two rows share the same rank, show the lowest value, (i. e.: instead of showing 2-3, 2-3, the above should return 2, 3).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica