Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
drj12345
New Contributor III

Show values greater than or less than in Pivot Table

I have a Pivot Table with Few Dimensions and the following Expressions as Columns

sum(Profit)

sum(Variance)

sum(Derived)

which are numbers

I have to create a button where if I select the button, the table must show only values greater than 2,00,000 and another button for 5,00,000.

How do I do it?

Is it possible to create three buttons for each column?

21 Replies

Re: Show values greater than or less than in Pivot Table

You can use variables and then based on it you can set a condition for your expression. Which of the three expression does this variable need to control?

Re: Show values greater than or less than in Pivot Table

Check if the attached sample helps:

drj12345
New Contributor III

Re: Show values greater than or less than in Pivot Table

Hi Sunny,

Thank you. I used the same approach. What happens if I want to apply the same filter rule (>2k or 5K) on all columns in the pivot?

Re: Show values greater than or less than in Pivot Table

Use the same if statement for all your expressions

drj12345
New Contributor III

Re: Show values greater than or less than in Pivot Table

Thanks Sunny. That was just a sample expression. The original expressions that I am using is

sum({$<Month = {'January 2016'},LOB={'Bank'}>} Profit)

The Expression which you have used in the qvw is

If(Sum(Measure) > vValue, Sum(Measure))

How do I write my expression in this case?

Re: Show values greater than or less than in Pivot Table

Like this:

If(Sum({$<Month = {'January 2016'},LOB={'Bank'}>} Profit) > vValue, Sum({$<Month = {'January 2016'},LOB={'Bank'}>} Profit))

drj12345
New Contributor III

Re: Show values greater than or less than in Pivot Table

Oh, so what I thought is right. We need to repeat the analysis again. Thanks Sunny. I will try this and let you know if works for my requirement

Re: Show values greater than or less than in Pivot Table

There might be other ways, but unless I see a sample to look at, difficult to say what might work

drj12345
New Contributor III

Re: Show values greater than or less than in Pivot Table

Hi Sunny,

Thanks for the solution. I am currently using the same technique in my dashboard. The problem arises when there are -ve values and 0 in the field. I have attached the same qvw file for your understanding.

Issues:

1. You can see that the button doesn't work if there are -ve values and 0s in the table.

2. if there are two measures as expressions, selecting the button shows values >200 in Measure 1 column but I can still see other Dimensions as they have values Measure2 column. I want to show only those dimensions whose sum(measure) value is >200 or >500.