1 Reply Latest reply: May 28, 2016 6:45 AM by Stefan Wühl

Nulls not being suppressed

Hi,

I've used a Pivot table to present my data.  The following is in the dimension:-

=If(WildMatch(Code, '*CALLBUS*'), Dual('Calls-BusHelp',3),
If(WildMatch(Code, '*CALLHR*'),DUAL( 'Calls-HR',2),
If(WildMatch(
,Code '*CALLS*'), DUAL('Calls',1),' ' )))

CALLHR & CALLS are summed from the same field.  CALLBUS is summed from a different field ( =Sum(Quantity)). I don't want to show =Sum(Quantity) under Calls or Calls-HR.

I've tried if(Quantity <>'',sum(Quantity),'') so that results returns NULL rather than zero.  And then making sure the suppress NULLs in both presentation and under dimension.  but so far nothing is working:-

 Calls Calls-HR Calls-BusHelp Account =SUM(Purchased) =SUM(Remaining) =SUM(Quantity) =SUM(Purchased) =SUM(Remaining) =SUM(Quantity) =SUM(Purchased) =SUM(Remaining) =SUM(Quantity) Dolly's Flowers 1 1 - 10 7 - 0 0 2 Justins Land 5 4 - 0 0 - 0 0 1

• Re: Nulls not being suppressed

if(Quantity <>'',sum(Quantity),'')

is not returning NULL (use NULL() function for that), but an empty value (which is different).

But you problem is different: You can't just hide an expression for some dimensional values, but show it for others in a Qlik pivot table chart. You can only show / hide / supress expression column for the entire chart (and that's not what you want).

But you might be able to work around, using another dimension that basically is replacing your three expressions, and a single expression that tests the 'expression surrogate' dimension value.

So this dimension would link three values 'Purchased', 'Remaining', 'Quantity' to your Calls-BusHelp dimension value, and only 'Purchased' and 'Remaining' to the other two.

To make the linking easier, I would suggest to create another field in your data model replacing the current calculated dimension:

Code,

If(WildMatch(Code, '*CALLBUS*'), Dual('Calls-BusHelp',3),
If(WildMatch(Code, '*CALLHR*'),DUAL( 'Calls-HR',2),
If(WildMatch(
,Code '*CALLS*'), DUAL('Calls',1),' ' ))) AS ChartDim,

...

FROM ...;

Then you could create a link table for your expression surrogate dimension:

ChartDim, ChartExpressionDim

Calls_BusHelp, Purchased

Calls_BusHelp, Remaining

Calls_BusHelp, Quantity

Calls-HR,Purchased

Calls-HR,Remaining

Calls,Purchased

Calls,Remaining

];

Then remove all three expressions and use a single expression:

=Pick(Match(ChartExpressionDim,'Purchased','Remaining','Quantity'),

Sum(Purchased),

Sum(Remaining),

Sum(Quantity)

)