Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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";
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.
This is how to show zero values.
Best regards
QlikView consultant at egbs consulting ab
Blog (in Swedish): bi-effekten.se
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";