6 Replies Latest reply: Jun 24, 2013 2:35 AM by test test

Handle Zero Values in Month Dimension

I have the following table which shows the sum of stock for each article, in that specific month.

 Month MF  10 10 MF  10 13 MF  10 15 MF  10 18 MF  10 20 MF  10 25 MF  10 30 MF  10 35 MF  10 40 MF  10 45 MF  10 50 jan 21 33 12 63 36 0 13 10 0 3 0 feb 14 27 35 51 32 38 11 8 0 0 0 mrt 12 25 70 0 20 60 23 0 0 0 0 apr 77 0 28 38 45 48 10 10 0 0 0 mei 0 0 114 35 60 37 0 8 1 0 0 jun 70 20 111 34 38 62 7 8 1 3 3

I want to accomplish this:

 Month MF 10 10 MF 10 13 MF 10 15 MF 10 18 MF 10 20 MF 10 25 MF 10 30 MF 10 35 MF 10 40 MF 10 45 MF 10 50 jan 21 33 12 63 36 ?? 13 10 ?? 3 ?? feb 14 27 35 51 32 38 11 8 0 3 0 mrt 12 25 70 51 20 60 23 8 0 3 0 apr 77 25 28 38 45 48 10 10 0 3 0 mei 77 25 114 35 60 37 10 8 1 3 0 jun 70 20 111 34 38 62 7 8 1 3 3

ofcourse in the example the ?? values will be subtracted from december the previous year.

How can i accomplish this?

• Re: Handle Zero Values in Month Dimension

Hi,

Perhaps you can achieve what you want with something like this:

If(Sum(Value)=0 or Len(Sum(Value))=0, Dual('??', 0), Num(Sum(Value), '#,##0'))

On the Presentation tab of the chart you should turn off the "Supress Zeros" option and on the Number tab you should make sure to select "Expression Default".

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

• Re: Handle Zero Values in Month Dimension

The problem is, in those months is no record, cause no mutations in the stock happened there.

Here is an example of a mutation:

 KeyPart MF 10 10 Date Voorraad Mutaties 21-6-2013 70 0 12-6-2013 70 -5 6-6-2013 75 -1 3-6-2013 76 -1 12-4-2013 77 65 5-3-2013 12 -2 15-2-2013 14 -2 13-2-2013 16 -5 30-1-2013 21 -6 11-1-2013 27 -3

For each values the mutations of that row are already subtracted

• Re: Handle Zero Values in Month Dimension

Hi,

Is it as simple as just setting the "Null Symbol" and "Missing Symbol" in the presentation tab to "??"?

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

• Re: Handle Zero Values in Month Dimension

i tried "??" and ?? but it just shows the symbols in the table

• Re: Handle Zero Values in Month Dimension

Hi,

It is difficult for me to answer your question without having access to your data and to the full requirements - and this is not the place to discuss those.

At this stage, I am not really sure what the query is that I am answering!

If a selection does not exclude a value, you should be able to make it appear in a chart.  However, the problem can be that selections will exclude the value and so it will disappear.

Perhaps you could go back a step and explain what your data are, and what you are trying to display.  Why do you need to display "??" at all?

If you are trying to display an exception report, I would think about how I could calculate the exceptions and display them in their own chart - otherwise they might get lost in a table of numbers.

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.