topic Re: Single MAX value in pivot table in QlikView

Single MAX value in pivot table

Fri, 09 Jan 2015 09:35:18 GMT

Hello everyone,

So, I have a pivot table with several results present in the picture 1.

The first 4 columns are regular dimensions and the 5th column (Hora do teste) is my expression.

The expression is: =time(max({<ZonaTeste={'PT1'},ResultadoTeste={'1'}>} HoraTeste))

What I would like to do is to show only the maximum value of "Hora do teste", like so:

I tested the expression on a textBox and returns only the maximum "Hora do teste" value, so this is OK. But I am missing something to make the expression work properly on the pivot table.

Can anyone help?

Thank you in advance.

Ivo Marques

Re: Single MAX value in pivot table

SunilChauhan — Fri, 09 Jan 2015 09:42:57 GMT

Date(max({<ZonaTeste={'PT1'},ResultadoTeste={'1'}>} HoraTeste))

Re: Single MAX value in pivot table

jonathandienst — Fri, 09 Jan 2015 09:51:31 GMT

Do you mean that the expression should return the maximum while ignoring the dimensions? Use TOTAL:

=time(max({<ZonaTeste={'PT1'},ResultadoTeste={'1'}>} TOTAL HoraTeste))

You can total over some subset of the dimensions by using something like:

=time(max({<ZonaTeste={'PT1'},ResultadoTeste={'1'}>} TOTAL <dim1, dim2> HoraTeste))

HTH

Jonathan

Re: Single MAX value in pivot table

Fri, 09 Jan 2015 09:54:19 GMT

Hello Sunil,

Thank you for the reply. I'm afraid that does not work.

What it does is format the time value to a date and all values are still in the table.

Ivo

Re: Single MAX value in pivot table

SunilChauhan — Fri, 09 Jan 2015 10:05:17 GMT

remove column Descrcao

Re: Single MAX value in pivot table

jagan — Fri, 09 Jan 2015 10:10:13 GMT

Hi,

Move the third dimension to expression using the below one

=MaxString(Description) OR MinString(Description)

You are getting duplicates because you are having multiple values in this dimension, to overcome this we are using expression to get the max value.

Hope this helps you.

Regards,

Jagan.

Re: Single MAX value in pivot table

Fri, 09 Jan 2015 10:14:54 GMT

Hello Jonathan,

Answering your question, the only thing I want to show of the dimensions are the values that correspond to the maximum value of "Hora do teste", like I have on the picture (Sorry for the blurred dimension values):

I tried to use TOTAL and this turned all the "Hora do teste" values to 11:17:17,but I have the same number of rows in the table.

Best regards.

Ivo

Re: Single MAX value in pivot table

SunilChauhan — Fri, 09 Jan 2015 10:58:29 GMT

create a variable

set Var=Max([Hora do tetse]);

in dimension in place of descricao write below expression

if([Hora do tetse]=$(Var),descricao ) and do supress null for this column

Re: Single MAX value in pivot table

tobias_klett — Fri, 09 Jan 2015 12:07:53 GMT

Hi Ivo,

you need to narrow the table to the maximum value like this:

=time(max({<ZonaTeste={'PT1'},ResultadoTeste={'1'},HoreTeste={$(=max(HoraTeste))} >} HoraTeste))

Hope that helps

Tobias

Re: Single MAX value in pivot table

Fri, 09 Jan 2015 13:36:58 GMT

Hello Tobias,

Your solution seems nice to me, but it suppresses all the values in the table.

I get a table with only '-'.

Thank you for the help.