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: 
Anonymous
Not applicable

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

1 Reply
swuehl
MVP
MVP

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)

)