topic Re: Show missing values as 0 in graph in QlikView

Show missing values as 0 in graph

Tue, 06 Nov 2012 07:52:38 GMT

Hi,

I have database table which has monthly customer values about sales, costs etc. For some customers there may be months that don't have data at all. In qlikview I create months and years as inline table and then select the monthly data from database there. Then I have line graph which shows sales, costs etc. for each selected month. The problem is that if I select customer which don't have data for all selected months then graph just don't show those months at all and I would like to display those month data as 0 in line graph.

Could anybody help? I have tried to put nulldisplay, left join, display missing and zero values etc. but I just can't get it working.

The script is like this at the moment (note that I have personal edition in use):

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='D.M.YYYY';

SET TimestampFormat='D.M.YYYY h:mm:ss[.fff]';

SET MonthNames='tammi;helmi;maalis;huhti;touko;kesä;heinä;elo;syys;loka;marras;joulu';

SET DayNames='ma;ti;ke;to;pe;la;su';

set NullDisplay=0;

Load * Inline [Month

1

2

3

4

5

6

7

8

9

10

11

12];

Load * Inline [Year

2012

2013];

ODBC CONNECT32 TO DB (XUserId is xxx, XPassword is xx);

left join

LOAD *,

(CustName & ' / ' & Text(CustNum)) AS Customer,

If (Sales=0, 0, ((Sales-DirVarLabCost-MaterialCost-PurPartCost-SubContrCost)/Sales*100)) AS GM%,

If (Sales=0, 0, ((MaterialCost+PurPartCost+SubContrCost)/Sales*100)) AS Part%,

If (Sales=0, 0, ((DirVarLabCost)/Sales*100)) AS Work%,

If (Sales=0, 0, ((MaterialCost)/Sales*100)) AS Mat%,

If (Sales=0, 0, ((QCost+QSales)/Sales*100)) AS Q%;

SQL SELECT CustName,

    CustNum,

    DirVarLabCost,

    Factory,

    MaterialCost,

    Month,

    PlanDirVarLabCost,

    PlanMaterialCost,

    PlanPurPartCost,

    PlanSubContrCost,

    PurPartCost,

    OtDirVarCost,

    PlanOtDirVarCost,

    DirFixCost,

    PlanDirFixCost,

    QCost,

    QSales,

    Sales,

    SubContrCost,

    Year,

    (Sales-DirVarLabCost-MaterialCost-PurPartCost-SubContrCost) AS GM,

    (DirVarLabCost-PlanDirVarLabCost) AS WorkDiff,

    ((MaterialCost+PurPartCost+SubContrCost)-(PlanMaterialCost+PlanPurPartCost+PlanSubContrCost)) AS PartDiff

FROM db.dbo."ACS_MonthlyData";

Re: Show missing values as 0 in graph

Vegar — Tue, 06 Nov 2012 14:30:59 GMT

You need to set NullAsValue. (Default is NullAsNull)

NullAsValue *; //null as value for all fields

NullAsValue month; //null as value for the month field

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

Re: Show missing values as 0 in graph

CELAMBARASAN — Tue, 06 Nov 2012 14:33:42 GMT

Hi,

Go to Chart properties -->Presentation tab -->Check the options Missing symbol and Null symbol provide the chars as you need.

Re: Show missing values as 0 in graph

Wed, 07 Nov 2012 06:32:50 GMT

Hi,

there's no missing or null symbol assignment in Presentation tab (see below)

I found those null and missing symbol setting in month and year listboxes and set them to 0 but it didn't work either.

And I added the NullAsValue *; and it didn't change anything. I also tried NullAsValue Month, Year; but no change.

It works like this I select month range and year and graph will show sales trend for that time range. If I select then customer which don't have data for all those months missing months get unselected from month range and that's why the graph only shows those months which have data (I think). So is there any way to prevent this happening?

Re: Show missing values as 0 in graph

jagan — Wed, 07 Nov 2012 07:21:01 GMT

Hi,

In chart

Select "Show All Values" for a dimension in Dimension tab.

UnSelect "Supress Zero Values" and "Supress Missing" in Presentation tab.

Check attached file for solution.

Regards,

Jagan.

Re: Show missing values as 0 in graph

Wed, 07 Nov 2012 07:37:58 GMT

Hi,

In graph properties I have those options unselected like you wrote.

BR,

Maarit

Re: Show missing values as 0 in graph

Wed, 07 Nov 2012 07:38:33 GMT

And I have QV personal edition so I can't check the example you sent me...

Re: Show missing values as 0 in graph

whiteline — Wed, 07 Nov 2012 19:46:54 GMT

Hi.

If you don't have data in that points (so that tha nulls() are calculated, there are no such rows in your data),

you can't show it.

To show something you should either have it as data or calculate it synthetically (in this case it could be rather complex).

Re: Show missing values as 0 in graph

CELAMBARASAN — Wed, 07 Nov 2012 20:42:36 GMT

You checked this in Bar chart type it is available in tabular form charts (Straight table & pivot).

Re: Show missing values as 0 in graph

Thu, 08 Nov 2012 06:36:04 GMT

Hi,

you are absolutely correct. I realized just now that there's not even a null row for customer if it doesn't have data for the month.

I can't come up with any other solution but to create monthly rows in to the database for those customers that already in db and don't have data for the current month.

Thanks for helping me to understand this.

Re: Show missing values as 0 in graph

Vegar — Thu, 08 Nov 2012 08:28:13 GMT

maarit.sailynoja wrote:
[...]Then I have line graph which shows sales, costs etc. for each selected month. [...]

There is an alternative sollution to adding data rows if you have a numeric dimension (e.g. month, date, year) in your line chart.

Create your line chart, under the axes properties check the "Continuos" check box. It will scale your axis as if as continous,

OBS. Value will not be 0 but just ignored. Take a look at the solution below.

Re: Show missing values as 0 in graph

Vegar — Thu, 08 Nov 2012 08:40:00 GMT

This is how to show zero values.

Make sure you got a calendar with all the months you want to display.(If you are missing data for 2012-06 make sure 2012-06 exist in your calendar.)
Enable Continuous dimension axis
Uncheck the Suppress Zero-Values under Presentation properties.

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

Re: Show missing values as 0 in graph

Thu, 08 Nov 2012 09:24:26 GMT

Hi,

this later one which shows missing values as zero is exactly the one I would need. But I cannot get it working.

It will still show me this like below (uncheckes the missing month from month selection and continues through the missing month in graph, not showing it as zero):

chart's properties are like below:

What I'm missing compared to your solution?

Here's my script at the moment:

SETThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='D.M.YYYY';

SET TimestampFormat='D.M.YYYY h:mm:ss[.fff]';

SET MonthNames='tammi;helmi;maalis;huhti;touko;kesä;heinä;elo;syys;loka;marras;joulu';

SET DayNames='ma;ti;ke;to;pe;la;su';

Years:

Load * Inline

[Year

2012

2013];

Months:

Load * Inline

[Month

1

2

3

4

5

6

7

8

9

10

11

12];

NULLASVALUE *;

ODBC CONNECT32 TO db(XUserId is xxx, XPassword is xxx);

left join

LOAD *,

(CustName & ' / ' & Text(CustNum)) AS Customer;

SQL SELECT CustName,

    CustNum,

    DirVarLabCost,

    Factory,

    MaterialCost,

    Month,

    PlanDirVarLabCost,

    PlanMaterialCost,

    PlanPurPartCost,

    PlanSubContrCost,

    PurPartCost,

    OtDirVarCost,

    PlanOtDirVarCost,

    DirFixCost,

    PlanDirFixCost,

    QCost,

    QSales,

    Sales,

    SubContrCost,

    Year,

    (Sales-DirVarLabCost-MaterialCost-PurPartCost-SubContrCost) AS GM,

    (DirVarLabCost-PlanDirVarLabCost) AS WorkDiff,

    ((MaterialCost+PurPartCost+SubContrCost)-(PlanMaterialCost+PlanPurPartCost+PlanSubContrCost)) AS PartDiff

FROM db.dbo."ACS_MonthlyData";