Nulls not being suppressed

Anonymous — Sat, 28 May 2016 02:21:33 GMT

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

swuehl — Sat, 28 May 2016 10:45:41 GMT

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:

LOAD

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:

LOAD * INLINE [

ChartDim, ChartExpressionDim

Calls_BusHelp, Purchased

Calls_BusHelp, Remaining

Calls_BusHelp, Quantity

Calls-HR,Purchased

Calls-HR,Remaining

Calls,Purchased

Calls,Remaining

];

Now create your pivot table chart replacing your calculated dimension with ChartDim and adding dimension ChartExpressionDim.

Then remove all three expressions and use a single expression:

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

Sum(Purchased),

Sum(Remaining),

Sum(Quantity)

)

topic Nulls not being suppressed in QlikView

Nulls not being suppressed

Re: Nulls not being suppressed