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: 
mborsadw
Partner - Creator
Partner - Creator

Hide Measure row for certain dimensions in a pivot table

clipboard_image_0.png

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.

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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

 

image.png

View solution in original post

4 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

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

 

image.png

mborsadw
Partner - Creator
Partner - Creator
Author

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.