Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
lorenzo_gibbo
New Contributor III

Pivot filtered by lowest number

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.

1 Solution

Accepted Solutions
Highlighted

Re: Pivot filtered by lowest number

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

View solution in original post

2 Replies
Highlighted

Re: Pivot filtered by lowest number

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

View solution in original post

lorenzo_gibbo
New Contributor III

Pivot filtered by lowest number

Hi Miguel,

thanks very much!!

Bye!