Struggling with a simple calculation

[cognosjon]

Hi,

Looking for some help with a small problem that I'm currently facing.

I have two fact tables one that provides Actuals and another that provides forecasts. These have then been merged into one query subject within Framework.
Actuals and Forecasts are at the same level of granularity with regards to date.

When I create a report against this I may get the following situation

Month      Actual      Forecast      Variance

January      1200      1000           200
February                 1500      
March      1000       1500          -500      


The variance is calculated simply by subtracting the actual from the forecast however as no actual sales value exists for February I get no variance result.
There is no Actual sales record that exists within the database(SQL) for January hence no value within the list report and my calculation fails. I have tried applying ' is missing' and 'is null' functions as part of an if statement to populate the vacant field with a 0, but these don't help due to no record existing within the database.

Does anybody have any suggestions as to how I can get round/solve this problem?

Thanks in advance

[almeids]

I'm guessing that your report is a crosstab?  Otherwise your attempts to convert null to zero should have worked.
One solution would be to supplement your existing data with all-zeroes result sets to fill in the gaps.  You could add a query to pull months from the Actuals table with calculated item "Forecast" for your columns dimension and calculated measure 0, and another to similarly pull from the Forecast table identified as "Actual".  Union these two with your existing query and the aggregation for the crosstab will fill in the holes with zeroes.

[cognosjon]

Hi and thanks for the quick reply, would like to say yes its a crosstab but it isn't, its a simple list report.

We have as a work around 'fudged' the data in the warehouse to do basically what go on to say and within the warehouse we eventually end up with lots of 0 being populated and as you'd expect my calculation then works.

However this isn't ideal and the warehouse chap I feel may blow a gasket.

Is what I'm trying to do feasible or am I having a really mind numbing day and should just simply go home?

[Suraj]

Simple solution is to replace null with 0 in your query items in FM for both actual and forecast.
Your report is trying to do a calculation on nulls and that's why it is blank.

[cognosjon]

Hi
I Understand what your saying but both my Actual and forecast items have the following applied to them

if([actual] is null) then (0) else([Actual])
if([forecast] is null) then (0) else([Forecast])

and I still return no nothing.

I think I'm either stuck with populating the warehouse with 0 where a value doesn't exist or the creation of a joined or union query.

[almeids]

Your actual and forecast values are coming from a single query, i.e. both values are in the same record?  If that's the case there's no apparent reason why checking for nulls shouldn't work as you've tried and Suraj suggests.
Take a look at the sql generated and see if there are any clues there, otherwise perhaps try using native database function isnull([actual],0) in the data item calculations rather than if/else, though that really shouldn't make a difference.

[Arsenal]

This null issue is a HUGE problem with Cognos. Coalesce etc. doesn't work and Cognos has very little support for this situation. We have entire sub total rows showing up as blanks (even with coalesce) because one of the ingredients in the sub total calculation is a null

null+anything = null makes sense, but what when we have tried everything to populate the null rows with 0's in the reports?

[rockytopmark]

try:

coalesce([your_Item],0)

[cognosjon]

Thanks for the replies chaps, but still no joy.

As I need to move quickly on this we have decided to adopt the population of the warehouse table with zeros where one object exists and the other doesn't. Not ideal I know but at least I can be guaranteed of the result that I will get at the report end(fingers crossed).

At least I feel better knowing that it wasn't my mind getting a NULL result when trying to figure out what was going on.

Anybody contacted Cognos support about these issues? If not then maybe I'll fire a question off to them. Let me know.