topic Re: 0 value for missing dates in App Development

0 value for missing dates

gerald_lebret — Wed, 09 Feb 2022 17:01:36 GMT

Hello Everyone,

I've been trying to solve a problem for days but can't manage to.

I have data with detailled transaction one one table and I have another table with complete calendar.

I need to create a pivot table that will show for each item a sum of the transaction per week (actually, I have to use set analysis as it is a bit more complex than just the sum for 1 field).

In case there are no transaction during the week, I need to show a 0.

As long as I show all the data, it's fine. However, from the moment I pick one item, it only shows the weeks where there are transactions.

I need to do it on Qlik Sense. I did try to get the expected result on qlikview without any success.

I've got millions of records and thus which contains thousands of items. Thus, it's not possible to create Fictive records.

Can someone please help??

Re: 0 value for missing dates

chris_djih — Fri, 11 Feb 2022 12:27:31 GMT

try Alt( Sum({<compelx Set-Analysis>} yourField) , 0).
Alt() functions tries to evaluate the expression and if succesfull return it as the result.
If it is not able to evaluate the expression, it tries the next expression - in your case "0" - and returns it as the result.

Re: 0 value for missing dates

gerald_lebret — Fri, 11 Feb 2022 12:59:14 GMT

I did try that before posting my question...It did not work.