Hide Measure row for certain dimensions in a pivot table

[mborsadw]

I want to hide the 'Demand' Measure row if the Item is 'A' but keep it if Item is 'B'. Can I do this in Qlik Sense?

I tried using "Show Column If" but that doesn't seem to work at individual dimensions.

Sample QVF is attached.

[dplr-rn]

you cant hide the row for just 1 dimension.

But you could use an if condition to display something like 'n/a' for item A

[sunny_talwar · Accepted solution]

Try this

Script

Data:
Load * inline [
Item, Measure, Dt, Value
A, Supply, 1/1/2019, 10
A, Supply, 1/2/2019, 10
A, Demand, 1/1/2019, 20
A, Demand, 1/2/2019, 20
B, Supply, 1/1/2019, 9
B, Supply, 1/2/2019, 7
B, Demand, 1/1/2019, 10
B, Demand, 1/2/2019, 11
];

Cross:
Generic Load Item, Dt, Measure, Value resident Data;

drop table Data;

Dim:
LOAD * INLINE [
Dim
1
2
];

Row Dimensions

Item
=Pick(Dim, 'Demand', 'Supply')

Column Dimension

Dt

Expression

Pick(Dim, If(Item <> 'A', Sum(Demand)), Sum(Supply))

Also, make sure to uncheck 'Include zero values' under Add-Ons -> Data handling. You will get this

[mborsadw]

Appreciate your quick response Sunny!

It does work but now I have to put all my measure calculations into one Pick expression

Pick(Dim, If(Item <> 'A', Sum(Demand)), Sum(Supply))

I have 10-15 calculations and it I assume it would become difficult to maintain.

Also I cannot reuse measures, for example

I have a measure called PI = Sum(Demand) + Sum(Supply) and then another measure DSI = PI/2. With regular measures I can define PI (in the pivot table) and then use it in another measure as PI/2 (in the pivot table). I lose that functionality with the PICK approach.

Anyway out of this pickle?

[sunny_talwar]

Unfortunately, no. But to reuse, you can always store your expression and use it as a variable.