topic Re: Handle Zero Values in Month Dimension in QlikView

Handle Zero Values in Month Dimension

Fri, 21 Jun 2013 13:27:09 GMT

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

stephencredmond — Fri, 21 Jun 2013 14:16:59 GMT

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

Re: Handle Zero Values in Month Dimension

Fri, 21 Jun 2013 14:29:08 GMT

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

stephencredmond — Fri, 21 Jun 2013 14:52:15 GMT

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

Re: Handle Zero Values in Month Dimension

Fri, 21 Jun 2013 14:58:07 GMT

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

Re: Handle Zero Values in Month Dimension

stephencredmond — Fri, 21 Jun 2013 15:08:43 GMT

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

Re: Handle Zero Values in Month Dimension

Mon, 24 Jun 2013 06:35:42 GMT

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