# App Development

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
Creator

## Show zero for no records

I'm preparing a presentation for the performance of our sales people. For example, I needed a number of tables (for different products) that show a list of our sales-people and what they sold in the last 6 months.

The result should look like so:

 Sales person Product Sales Peter A 100 Karl B 10 Susan A 200 Martin C 0

But when I filter the timeframe and the product, all sales people with zero sales disappear in the table

 Sales person Product Sales Peter A 100 Karl B 10 Susan A 200

(Martin disappeared because he has no sales records in the selection)

I basically need all 4 sales people to always show up in the table even if the selections return no records and zero for sales in those cases.

Anybody know how this can be achieved?

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
MVP

You could try either of the following - the former ignores all selections and the latter only on specific field(s). Note that "Include zero values" must be checked under Data Handling.

Sum(Sales)+Sum({1} 0)

Sum(Sales)+Sum({< Month= , Product=>} 0)

7 Replies
MVP

You could try either of the following - the former ignores all selections and the latter only on specific field(s). Note that "Include zero values" must be checked under Data Handling.

Sum(Sales)+Sum({1} 0)

Sum(Sales)+Sum({< Month= , Product=>} 0)

Creator
Author

Hello Or,

thank you for your reply. What I didn't make clear in my original post was, that I have to have a selection for products as well. Sorry for that. We have several thousand products and and I only want to select a small subset of them (say 4) for a particular analysis.

MVP

What is your expectation in the scenario where products A and B were selected but C was not? Martin's only row is for product C, so if you aren't displayed non-selected products, what would his row(s) look like?

Creator
Author

Hello Or,

If C was not selected, C should not show up. So if I did an analysis for just product A, I want to select A, show all 4 sales people but Karl and Susan with zeros (instead of them not appearing).

I think I've also just found a way.

1st Dimension

``[Sales people]``

2nd Dimension

``=if(Match(product, \$(=Chr(39) & GetFieldSelections(product,Chr(39)&','&Chr(39),12) & Chr(39) ) ),product)``

edit:

I found the solution for this here: https://community.qlik.com/t5/New-to-Qlik-Sense/Getfieldselections-with-multiple-values-in-if-condit...

Measure

``````sum(
Sales
)+
Sum(
{<Month=, Year=, product= >}
0)``````

MVP

If you've found a solution, great. There's quite a few different ways to go about doing something like this and comparing with GetFieldSelections is indeed one of them, though I'd suggest you consider comparing with a concat() of the values instead as it is a more robust option (concat will work if you've implicitly selected the products, for example by selecting Product Group = 'AB' which includes products A and B, but did not explicitly select a product).

If not, I'm still not sure what your expectation is. Since both A and B were selected, are you expecting one 0 row for each salesperson for A and another one for B?

Creator
Author

Hello Or,

could you tell me how it would work with concat? I could not figure it out.

MVP

concat(distinct Field) just replaces GetFieldSelections(Field) inside the match. Otherwise everything stays the same.

Tags
Community Browser