Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Graph not displaying null values. Please help!

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!

QlikView_Dates.png

Thank you in advance to anyone who has any suggestions!

10 Replies
Not applicable
Author

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.

Not applicable
Author

Check if in Dimensions tab is marked the option of the null values, if so, unmark it, this could solve your problem if not, please upload your App to see it deeply.

Hope it helps!!

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

Not applicable
Author

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!

Regards


Edward

Not applicable
Author

Find the attached App

Not applicable
Author

Hi wishes29,

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!

Regards


Edward

subin6600
Creator III
Creator III

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.

BR/Subin

Not applicable
Author

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 *

FROM Project.par.Projects

WHERE Status = 'Live'

AND Horizontal = 'PTS';

LOAD

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;

subin6600
Creator III
Creator III

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

dateMaterial code
3/4/2012899
7/9/2012890

&

material  code material  description
899toothbrush
890toothpaste

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.

Regards,

Subin