Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

2 Replies

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

lorenzo_gibbo
New Contributor III

Pivot filtered by lowest number

Hi Miguel,

thanks very much!!

Bye!

Community Browser