Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
BI Consultant
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.
BI Consultant
Hi Miguel,
thanks very much!!
Bye!