How to show 0 as value for weeks in which there are no facts?

[#1]

Dear community,

I have a problem, that I solved with a "strange" expression. I'm quite sure that this "workaround" works for me like it should, but I like to get some feedback from here to learn if there are better ways to solve it (and if there are risks using this approach).

Scenario:

- I have an amount in my fact table on certain dates

- I want to show a chart  with per week with sum(amount)

- I want to see 0 as value for the weeks where there is no record in my fact-table associated to this week

I know there are some checkboxes "suppress nullvalues / show all values" but they didn't work for me (yet), because my sum(amount) has a set-analysis on the field "task"

Please find the attached .qvw file where with a small dataset, my problem is depicted clearly

• 1 chart works like I want it (I select a period, the listbox Week shows the associated week, and in this chart (above) for all weeks I see a value)
• 1 chart does NOT work like I want it
• The working chart has a strange expression... The expression is

=sum({<Task={"BTL"}>} Amount)+sum(Amount)-sum(Amount)

• when I leave out +sum(Amount)-sum(Amount) the weeks without facts are missing again (...)

Please let me know if there are better ways to solve this.

• What I would like to AVOID is a left join of my fact-table to the MasterCalendar.
• Via google I found approaches like add 0.0000000001 and make numberformat with 2 decimals... I don't like that approach either (it is dirty)

I'm curious if another (better) approach exists!!

Also if you're convinced that this approach does NOT exist, please let me know.

Thanks in advance!

Roberto

[datanibbler]

Hi Roberto,

I have the same problem ion some cases, I think it's very common - QlikView is stubborn as regards displaying non-existent values 😉

I now usually insert one additional dummy_formula, usually with the value 0 or 0.001 or so, in my charts, so that for every data_point on the dimension, there is something to display and QlikView will display the data_point anyway.

=> In case that can't be done, I write an IF_condition in the expression, something like this

>>> IF([expr] <> 0, num([expr], [format]), num(0.01, [format])) <<<

I think there are more elegant ways, but this works fine for me and I find it rather easy.

Best regards,

DataNibbler

P.S.: Have a look at the help_file, keyword "NULLASVALUE()". That seems to be the most elegant way of fixing this.

[Nicole-Smith]

This blog may be of help: http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field