topic Re: Nulls and zeros in Aggr() function in QlikView

Nulls and zeros in Aggr() function

Thu, 08 Aug 2013 13:10:38 GMT

Hi,

Please can someone help? I have developed a pivot table with:

Dimensions: Customer, Product, Year
Expression: sum(aggr(sum([Quantity Ordered]), Customer, Product, Year))

I would like to:

Replace the null values with zero in the pivot table
Allow the user to filter where Quantity Ordered is zero

I attach the pivot table and my data model.

Many thanks

Re: Nulls and zeros in Aggr() function

Gysbert_Wassenaar — Thu, 08 Aug 2013 13:24:34 GMT

Displaying nulls as zero's can be done by changing the Null Symbol from a - to 0 on the Presentation tab.

It's not possible in QV to select nulls. Only real values can be selected. If you really want that you'll have to create real zero values. That would probably mean creating a table with a cartesian product of customer*product*year and replacing nulls with zero's. That's probably not a good idea.

Re: Nulls and zeros in Aggr() function

Thu, 08 Aug 2013 13:27:26 GMT

see image

in red area of image write 0.

Re: Nulls and zeros in Aggr() function

Thu, 08 Aug 2013 13:35:18 GMT

Thanks for your suggestions on replacing the null / missing value with 0 on the presentation tab. However that is only a string, not a numeric zero. I need to create real zero values which can be used in filters as Gysbert suggests. Can you please elaborate on how I would do this? Surely it is a common requirement to be able to select customers who have not ordered within a given year?

Re: Nulls and zeros in Aggr() function

Gysbert_Wassenaar — Sat, 10 Aug 2013 19:28:56 GMT

Surely it is a common requirement to be able to select customers who have not ordered within a given year?

Why do you need to select them? You can see those customers by simply selecting the year and look at the customers listbox to see which customers are shown as excluded in the listbox (having a grey background instead of white). But if you do need to actually select them, then first right click the customers list box choose Select Excluded and if necessary clear selections in the other fields.

It's also possible to use set analysis to create a search string for these customers for a Select in Field action. For example ='(' & concat({<Customer=e({<Year={2013}>}Customer)>} distinct chr(34) & Customer & chr(34), '|') & ')' for a search string of customers that didn't order in 2013.