12 Replies Latest reply: Nov 8, 2012 4:24 AM by Maarit Sailynoja RSS

    Show missing values as 0 in graph

      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";