
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Handling Null Values in Set Analysis
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can try sum({$<Sales_Value -= {'*'}>}Sales_Value). If this doesn't work try sum({$-<Sales_Value={'*'}>}Sales_Value)
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Try like this
=Sum({<Sales_Value
-={"*"}>}Sales_Value)
Hope it helps
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
