topic Set Analysis: How to remove null values in a straight table in QlikView

Set Analysis: How to remove null values in a straight table

Fri, 28 Aug 2009 15:14:05 GMT

Hi People,

Currently i have a little problem concerning applying set analysis in my straight table.

The situation is as follows:

I use QV 8.5.

I create straight table.

I have 1 dimsension called "Collection".

I have multiple expressions like sum(SalesAmount) , sum(receipts) etc. However, 1 of my expressions contains set analysis as follows:

sum( {1} SalesAmount)

This expression must show the end user the total sales amount at whatever selection is chosen. It works fine.

Nevertheless, the straight table shows an enormous amount of white (null)-values when i use the chart to analyse data. Note that the dimension contains the following setting: suppress null-values.

When i deactivate the set analysis expression, the null-values are suppressed correctly. So my question is how to suppress the null-values in case of an set analysis expression.

Thanks for your help in advance!

Set Analysis: How to remove null values in a straight table

johnw — Sat, 29 Aug 2009 02:04:34 GMT

Since the {1} set says to ignore your selections, it will pretty much always return a value, even when the other columns are null. So I'm betting that changing the expression to something along these lines would fix the problem:

if(column(1) or column(2) or...,sum({1}SalesAmount))

Basically, it says to only calculate the sales amount if one of the other columns is already established.

Set Analysis: How to remove null values in a straight table

Mon, 31 Aug 2009 14:33:50 GMT

Ho John,

First of all, thank you for your reply. I've tried your solution for the problem. However, it doesn't seem to work. Have you tried it yourself yet? Maybe I haven't explained my question clear enough?

If you want me to make a sample app to make things clear then I'll do that. However, maybe you've more tips for me.

Regards,

Tjeerd

Set Analysis: How to remove null values in a straight table

johnw — Tue, 01 Sep 2009 02:24:18 GMT

No, I hadn't tried it myself. But I have now, and it's working exactly like I expected. So perhaps I'm not understanding your problem still. Here's the example of what I THOUGHT your problem was, and my solution to that.