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

[#1]

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!

[johnw]

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.

[#3]

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

[johnw]

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.