22 Replies Latest reply: Nov 6, 2017 10:42 AM by Oliver Franz

# Ignore Dimension-Filter for If Statement in Pivot Table

Hey there,

I have an really frustrating problem and hope you might be able to help me.

The following example does not make much sense, but is a downsized / abstract version of my problem.

Table:

[Dimension1,Dimension2, Value

A,XX,1

B,YY,2

C,ZZ,3];

Now I create a new sheet with

• Pivot-Table
• Row: Dimension1
• Colum: Dimension2
• Expression: if(Dimension2 = 'ZZ', 15, Sum({1}Value))
• Filter
• Dimension1

This works great if there is no filter set.

But is not working anymore as soon as i use a filter.

I want to have that table completely independent from any filters that are used.

Do you have any idea of how to achieve this?

Thanks very much for your help!

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

Maybe this

if(Only({1}Dimension2) = 'ZZ', 15, Sum({1}Value))

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

unfortunately this solution has a weird problem: When you set a filter and do a reload, the values in the table change / update.

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

That should not have to do anything with the proposed solution. Have you tried to compare the two expressions after reloads? they give different outputs?

if(Dimension2 = 'ZZ', 15, Sum({1}Value))

if(Only({1}Dimension2) = 'ZZ', 15, Sum({1}Value))

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

you are right it should not, but it does. I reported it to Qlik already. You can try it out by yourself if you want to. (tested on September 2017 Release)

Data:

[Dimension1, Value

A,1

B,2

C,3];

Extra_Dimension:

[Dimension2

XX

YY

ZZ

];

Reproduction

2) Create new Sheet

3) Insert Pivot Table

a. Dimension Row: Dimension1

b. Dimension Column: Dimension2

c. Measure: if(Only({1}Dimension2) = 'ZZ', 15, Sum({1}Value))

4) Inset Filter for Dimension1

5) Select B in Dimension1 filter panel

6) Open Data Editor and click Reload

PROBLEM: Values have changed!

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

1) Is your data model this?

Table:

[Dimension1,Dimension2, Value

A,XX,1

B,YY,2

C,ZZ,3];

or this

Data:

[Dimension1, Value

A,1

B,2

C,3];

Extra_Dimension:

[Dimension2

XX

YY

ZZ

];

I assume that it is the 1st one based on the looks of the chart....

I tried it and values are not changing for me... sample attached

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

The first one is the test data model.

The second one comes much closer to my real problem. And the second one definetely produces the described issue.

The good thing is, that Andreas Formular is working for my pivot table. Even it does not work for my barchart.

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

I guess it is good that one of the option is working, but even with the data model change, I am seeing the numbers change... I mean what exactly do you see changing? Dimension1 = B becomes 4, 4, 30 from 2, 2, 15? or what?

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

That is strange... I reloaded 3 times, saw the same result for ZZ... can you try to reload the attached qvf?

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

Hey,

same behavior with your App. Changing the filter does not change the pivot table. Keeping the filter and doing a reload updates the table.

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

What version of Qlik Sense are you using?

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

I am using September 2017 - 11.14.3

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

Just downloaded September 2017 Patch 1 (11.14.4) and yet again not seeing the issue... anyways I am not sure what might be causing this, but if Andrea's solution is working, you can use that... but as far as I know, Pratyush's expression should not change after reload

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

maybe this:

=Sum( {1} if(Dimension2 = 'ZZ', 15, Value) )

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

Hey,

this works great for the pivot table! Thanks very much!

Unfortunately I need to present this data in a barchart too and your formular does not work as intended, because it just greys out the bars. :/

Do you have any idea of how to show the full barchart?

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

what's your expected result for the bar chart?

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

the bars should be thesame as in the picture, but they should still be coloured and not greyed out.

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

set also your color expression as custom by expression and type in Sum( {1} if(Dimension2 = 'ZZ', 15, Value) ).

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

Hey Andrea,

the problem is that I would like to define the colors manually:

if(Dimension2='XX', rgb(255,0,0),rgb(0,0,255))

Unfortunately i do not see any way to enhance that with a set analysis.

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

Try this for color

if(Only({1}Dimension2)='XX', rgb(255,0,0),rgb(0,0,255))

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

you can achieve it using a color by expression as below:

sum( {1} if( Dimension2='XX', rgb(255,0,0), rgb(0,0,255) ) )

and ticking "The expression is a color code".

• ###### Re: Ignore Dimension-Filter for If Statement in Pivot Table

Thanks Andrea. This  was really a mess. Had to add some minor adjustments to fit my needs, but you defineteley solved this!

THANK YOU VERY MUCH!