topic Re: Hide Measure row for certain dimensions in a pivot table in App Development

Hide Measure row for certain dimensions in a pivot table

mborsadw — Fri, 02 Apr 2021 08:05:27 GMT

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.

Re: Hide Measure row for certain dimensions in a pivot table

dplr-rn — Fri, 30 Aug 2019 12:49:31 GMT

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

Re: Hide Measure row for certain dimensions in a pivot table

sunny_talwar — Fri, 30 Aug 2019 13:54:59 GMT

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

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

Re: Hide Measure row for certain dimensions in a pivot table

mborsadw — Fri, 30 Aug 2019 17:47:29 GMT

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?

Re: Hide Measure row for certain dimensions in a pivot table

sunny_talwar — Fri, 30 Aug 2019 18:14:08 GMT

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