I am relatively new to QlikView but I have been spending a considerable amount of time unsucessfully trying to solve the following issue:
I have a database of financial reporting information. For some months there is no data whatsoever for a project, but I need to be able to see this visually. When I create a graph, it does not display the missing data as being zero! This is a serious problem. How can I make the chart display zero for the particular month for which there is no data?
As you can see in the following graph (which i have also attached), I have selected a project and you can see in the MonthMaster table that there is no data for March. But the graph just skips over the month as if it doesn't exist!
Thank you in advance to anyone who has any suggestions!
This issue is common, you need to create a master calendar, this master calendar have all dates, months, years since date you want.
This master calendar must be linked to your date field.
Then use the period, or date of master calendar that have all dates, including when you haven't data.
Later in graphic must deselect "Delete Null Values"
Here attach a qvw that contain a master calendar, and you can see how to create it.
Sorry for my bad english and hope this help you.
Thanks everyone for your replies. I did manage to create a master calender, but that did not help at all. All it did was replicate the issue I am already experiencing. I've attached a copy of the document that im working on. I've had to reduce the file size and scramble the data for obvious reasons but it still shows the problem!
Thank you for creating this for me. It still does not seem to work though. I noticed in the script the comment " ReportingMonth from SQL table also should be in YYYYMMDD format and linked with this". I'm not in a position to change the format of the date as it comes from SQL. But is this all i need to do in order for this to work?
I suspect what you have provided is the right answer and im just not understanding it. Sorry if that is the case!
You can change the format of the date which is coming from SQL by just using the below script
Date(<The Field Name>,'YYYYMMDD') as [<The Field Name>].
With this it would link to the calender as suggested by others. Hope it helps.
Thanks, that did work in changing the format but it caused other problems. ReportingMonth is in a table called FSR. When I add this to the script, it only causes ReportingMonth in the FSR table to be loaded. It is not linked to any other table, causing a 'calculation condition unfulfilled' message in the graph.
Here is the relevant portion of the script:
ODBC CONNECT TO MPR_Report;
SQL SELECT *
WHERE Status = 'Live'
AND Horizontal = 'PTS';
Date(ReportingMonth,'YYYYMMDD') as [ReportingMonth];
SQL SELECT FSR.*, QMS.TotalProfit AS 'QmsTotalProfit', QMS.PriceForecast AS 'QmsPriceForecast'
FROM Project.par.FSR FSR
INNER JOIN Project.par.Projects P
ON FSR.Project = P.Project
INNER JOIN Project.par.FSR QMS
ON QMS.Project = FSR.Project
WHERE P.Status = 'Live'
AND P.Horizontal = 'PTS'
AND FSR.DataFlag = 'Month'
AND QMS.DataFlag = 'QMS'
AND FSR.ReportingMonth = QMS.ReportingMonth;
You are loading only the ReportingMonth field and so it cannot automatically join with the other table data. So you need to even add the other fields in the load which is unique or combines both the table
Suppose you got two tables
|material code||material description|
So, you can modify the date as I already mentioned but also you need to load Material Code for joinng it.
Hope you understood what I tried to explain.