topic Re: Handling Null Values in Set Analysis in QlikView

Handling Null Values in Set Analysis

Anonymous — Tue, 02 Jul 2013 16:54:15 GMT

I have Sales in a table where a null value represents no sales and a zero value represents sales = returns (i.e., I want to load the null values). I tried the set analysis as below

Sum ({$< Sales_Value = {">$(#vMinSales)"}>} Sales_Value)

However, the set analysis ignornes the null values (i.e., the dimensions associated with the null values are not included in table). I tried

 sum ({$<if(isnull(Sales_Value),0,Sales_Value) = {">$(#vMinSales)"}>}Sales_Value)

but that has some type of syntax error (even though the expression builder says it's OK) that returns no results.

What syntax/function should I be using to include the null values. BTW, deselecting "Suppress Missing Values" does not fixt the problem.

Re: Handling Null Values in Set Analysis

Gysbert_Wassenaar — Tue, 02 Jul 2013 17:35:47 GMT

You can try sum({$<Sales_Value -= {'*'}>}Sales_Value). If this doesn't work try sum({$-<Sales_Value={'*'}>}Sales_Value)

Re: Handling Null Values in Set Analysis

MayilVahanan — Tue, 02 Jul 2013 17:37:18 GMT

Try like this

=Sum({<Sales_Value-={"*"}>}Sales_Value)

Hope it helps

Re: Handling Null Values in Set Analysis

Anonymous — Tue, 02 Jul 2013 18:44:04 GMT

The first expression returns no results, and the second returns ONLY null results. What I'm trying to do in this expression Sum ({$< Sales_Value = {">$(#vMinSales)"}>} Sales_Value), however, is sum the sales when they are above the miminum variable where null sales would be treated as zero sales. Therefore, sales above negative $5,000 would include the null sales. Sales above positive $5,000 would not include the null sales. I expected

Sum ({$<if(isnull(Sales_Value),0,Sales_Value) = {">$(#vMinSales)"}>} Sales_Value) to give the right results, but QlikView does not appear to like using function outputs in the Set Modifier.

Re: Handling Null Values in Set Analysis

kaushiknsolanki — Wed, 03 Jul 2013 06:05:43 GMT

Hi,

If all values are coming from one table in your data model, all you can do is create a flag in script which will help you to identify the Sales Return and Sales.

Something like this.

If(isnull(Sales),0,Sales) As Sales_Amount,

if(Isnull(Sales),'Sales',

if(Sales = 0, 'Return','Sales')) as Sales_Return_Flag.

Now all you have to do is use this flags in set analysis.

Hope this is clear.

Regards,

Kaushik Solanki