Handling Null Values in Set Analysis

[Anonymous]

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.

[Gysbert_Wassenaar]

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

[MayilVahanan]

Hi

Try like this

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

Hope it helps

[Anonymous]

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.

[kaushiknsolanki]

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