Nulls and zeros in Aggr() function

[#1]

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:

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

I attach the pivot table and my data model.

Many thanks

[Gysbert_Wassenaar]

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.

[#3]

see image

in red area of image write 0.

[#4]

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?

[Gysbert_Wassenaar]

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.