topic Re: Graph not displaying null values. Please help! in QlikView

Graph not displaying null values. Please help!

Tue, 08 May 2012 15:47:42 GMT

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!

Re: Graph not displaying null values. Please help!

Tue, 08 May 2012 19:56:17 GMT

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.

Graph not displaying null values. Please help!

Tue, 08 May 2012 21:16:22 GMT

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!!

Graph not displaying null values. Please help!

phaneendra_kunc — Wed, 09 May 2012 01:38:02 GMT

Hi,

Check these threads...

http://community.qlik.com/message/38180#38180

http://community.qlik.com/message/61255#61255

Regards,

Phani

Re: Graph not displaying null values. Please help!

Wed, 09 May 2012 09:43:07 GMT

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

Re: Graph not displaying null values. Please help!

Wed, 09 May 2012 11:39:43 GMT

Find the attached App

Re: Graph not displaying null values. Please help!

Wed, 09 May 2012 13:26:54 GMT

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

Graph not displaying null values. Please help!

subin6600 — Wed, 09 May 2012 13:31:11 GMT

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

Re: Graph not displaying null values. Please help!

Wed, 09 May 2012 14:38:14 GMT

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;

Graph not displaying null values. Please help!

subin6600 — Fri, 11 May 2012 09:33:08 GMT

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

date	Material code
3/4/2012	899
7/9/2012	890

material code	material description
899	toothbrush
890	toothpaste

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

Re: Graph not displaying null values. Please help!

Fri, 11 May 2012 13:04:52 GMT

Thanks. I think you misread my previous post though. I was saying that the added code caused the table to only have one item in it. I completely understand that the tables need to be joined.

I have actually managed to fix the problem by editing the SQL load script. I added "WHERE ISNULL(FSR.DataFlag, 'Month') IN ('Month', '');" on the end.

----------------------------------------------------------------------------

FATCA UK

Morrison Govan FATCA specialists