Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Register for Events and Webinars directly from Qlik Community: http://bit.ly/2Vpnenx
Not applicable

Nulls and zeros in Aggr() function

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

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Nulls and zeros in Aggr() function

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.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Nulls and zeros in Aggr() function

see image

in red area of image write 0.

Highlighted
Not applicable

Re: Nulls and zeros in Aggr() function

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?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Nulls and zeros in Aggr() function

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.


talk is cheap, supply exceeds demand