Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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.

1 Solution

Accepted Solutions
Highlighted

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
Highlighted
Partner
Partner

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

Highlighted

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

Highlighted
Partner
Partner

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?

Highlighted

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