Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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?

sunny_talwar

Check if the attached sample helps:

Anonymous
Not applicable
Author

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?

sunny_talwar

Use the same if statement for all your expressions

Anonymous
Not applicable
Author

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?

sunny_talwar

Like this:

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

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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.