Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Oliver_F
Partner - Creator II
Partner - Creator II

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:

load * inline

[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.

dimensionproblem1.PNG

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

dimensionproblem2.PNG

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!

1 Solution

Accepted Solutions
agigliotti
Partner - Master III
Partner - Master III

maybe this:

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

View solution in original post

22 Replies
prat1507
Specialist
Specialist

Maybe this

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

agigliotti
Partner - Master III
Partner - Master III

maybe this:

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

Oliver_F
Partner - Creator II
Partner - Creator II
Author

Hey thanks for your help,

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

Oliver_F
Partner - Creator II
Partner - Creator II
Author

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?

dimensionproblem3.PNG

sunny_talwar

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))

Oliver_F
Partner - Creator II
Partner - Creator II
Author

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)

Load Script

Data:

load * inline

[Dimension1, Value

A,1

B,2

C,3];

Extra_Dimension:

load * inline

[Dimension2

XX

YY

ZZ

];

Reproduction

1) Load Data

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!

sunny_talwar

1) Is your data model this?

Table:

load * inline

[Dimension1,Dimension2, Value

A,XX,1

B,YY,2

C,ZZ,3];

or this

Data:

load * inline

[Dimension1, Value

A,1

B,2

C,3];

Extra_Dimension:

load * inline

[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

Oliver_F
Partner - Creator II
Partner - Creator II
Author

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.

sunny_talwar

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?