Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
)