Single MAX value in pivot table

[#1]

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

[SunilChauhan]

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

[jonathandienst]

Hi

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

[#4]

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

[SunilChauhan]

remove  column Descrcao

[jagan]

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.

[#7]

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

[SunilChauhan]

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

[tobias_klett]

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

[#10]

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.