topic Re: Show zero for no records in App Development

Show zero for no records

fzalexanderjohn — Wed, 17 Aug 2022 08:51:16 GMT

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?

Re: Show zero for no records

Or — Wed, 17 Aug 2022 09:10:35 GMT

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)

Re: Show zero for no records

fzalexanderjohn — Wed, 17 Aug 2022 09:18:18 GMT

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.

If I use your solution, all products show up... which is expected.

Re: Show zero for no records

Or — Wed, 17 Aug 2022 09:26:44 GMT

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?

Re: Show zero for no records

fzalexanderjohn — Wed, 17 Aug 2022 12:09:59 GMT

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-condition/td-p/112080

Measure

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

Re: Show zero for no records

Or — Wed, 17 Aug 2022 12:04:58 GMT

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?

Re: Show zero for no records

fzalexanderjohn — Wed, 17 Aug 2022 14:24:26 GMT

Hello Or,

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

Re: Show zero for no records

Or — Wed, 17 Aug 2022 15:32:30 GMT

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