Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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:

MonthMF 10 10MF 10 13MF 10 15MF 10 18MF 10 20MF 10 25MF 10 30MF 10 35MF 10 40MF 10 45MF 10 50
jan2133126336??1310??3??
feb142735513238118030
mrt122570512060238030
apr7725283845481010030
mei7725114356037108130
jun702011134386278133

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

How can i accomplish this?

6 Replies
Luminary
Luminary

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.

Follow me on Twitter: @stephencredmond


Not applicable

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:

KeyPartMF 10 10
DateVoorraadMutaties
21-6-2013700
12-6-201370-5
6-6-201375-1
3-6-201376-1
12-4-20137765
5-3-201312-2
15-2-201314-2
13-2-201316-5
30-1-201321-6
11-1-201327-3

For each values the mutations of that row are already subtracted

Luminary
Luminary

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.

Follow me on Twitter: @stephencredmond

Not applicable

Re: Handle Zero Values in Month Dimension

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

Luminary
Luminary

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.

Follow me on Twitter: @stephencredmond


Not applicable

Re: Handle Zero Values in Month Dimension

With '??' i mean the value of the previous month.

The problem is that if there is no transaction in a certain month, the value of that month will be 0. So if I sum the totals of that month, the result is incorrect. Cause the 0 value is added