Pivot filtered by lowest number

[lorenzo_gibbo]

I Load from a database a data like this table:

Code	Sequence	Color
A	1	Yellow
A	1	Red
A	2	Blue
A	3	Green
B	2	Red
B	3	Green
C	3	Yellow

I need to display in a pivot only the Code corresponding at the lowest number of sequence, as in the example below:

Code	Sequence	Color
A	1	Yellow
A	1	Red
B	2	Red
C	3	Yellow

Do you have any suggestions for a formula set in the pivot which allows me to filter the data in this way?

Thanks all.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Lorenzo,

Using Aggr() and Min() to get the right values as a calculated dimension will work. Create a new chart straight table object, set Code and Sequence as dimension and add the following calculated dimension

=If(Aggr(NODISTINCT Min(Sequence), Code) = Sequence, Color)

Click on this calculated dimension and check "Suppress When Value Is Null"

Now add any dummy dimension (for example, "1") and go to the Presentation tab, and hide this expression. Note that as any calculated dimension it may take long to render if the data volume is huge, and you will get better results passing this aggregation on to the script part.

Check the attached application.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[lorenzo_gibbo]

Hi Miguel,

thanks very much!!

Bye!