Show Zero in Table for Sum

[JustinDallas]

Hello Everyone,

I have a table where I would like every dimension to have a '0' when it doesn't meet the Set Analysis requirement.  For instance, I have this dataset.

TestData:
LOAD * Inline
[
'UserId', 'ProductType','Count'
    1, 'Cookies', 3
    1, 'Biscuits', 4
    2, 'Peas', 5
    2, 'Sugar', 6
]
;


EXIT Script
;

Then, I have a table showing the UserId and a Set Analysis value of 'SUM({<[ProductType]={'Peas'}>} Count)'.  When a UserId doesn't have any Peas, the table throws the row away, giving me this.

What I would like is something like this.

And of course, I've got that 'Include Zero Values' checked (but this isn't quite a zero value, it's more like some kind of Null).

Any help is greatly appreciated.

[sunny_talwar · Accepted solution]

May be try this

Sum({<[ProductType]={'Peas'}>} Count) + Sum({1} 0)

[sunny_talwar]

[JustinDallas]

Ha!  That's it!  Thanks, I spent all night battling this problem to no avail.

[Jorge_Iturra]

Hey, this work on qlik cloud as well ! 

Thank you very much.