If you are unable to create a new account, please email support@bspsoftware.com



MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Calculating AVG with missing data in DM

Started by andigrill, 23 Dec 2022 07:07:28 AM

Previous topic - Next topic



how can I calculate the avg of a measure with missing data?

11/01/2022    2
11/02/2022    4
Sum    3

For 11/03/2022 there is no data, so the avg is calculated with 2 and 4, But I want the missing data used as 0 and the avg schould be 2.

I tried a measure as
    coalesce(total [Measure Column] /count (distinct [Date Column]),0)
but I got complete wrong results.

So I tried to calculate each part itself and create a calculated measure but somehow I get the wrong number of dates:

  Dim1.A  Dim1.B  Sum
  2022/11/01  2022/11/03  2022/11/05  Sum  2022/11/01  2022/11/03  2022/11/05 Sum  2022/11/01  2022/11/03  2022/11/05 Sum
AVGMeasure (Measue1/Number of Days)  1.813  1.870  1.878  1.854    456    450    452    453  1.135  1.160  1.165  1.153
Measure (Sum)  1.813  1.870  1.878  5.561    456    450    452  1.358  2.269  2.320  2.330  6.919
Number of Days      1      1      1      3      1      1      1      3      2      2      2      6

"Measure (Sum)" is the summarized Measure

"number of Days" is a calculated Measure 'count (distinct [Date])'
However, I didn't find a way for "Number of Days" to not summerize over other Dimensions (2 2 2 6  should be 1 1 1 3)!
When I tried default "number unique values" for [Date] I don't get a value for the Sum.

I'm at my wits end. I believe I can't see the forest for the trees.

Does anyone have an idea what's wrong?


You haven't really defined a general requirement.  That would help.  The little bit you provided seems to indicate this is a requirement for a specific report, not for the framework manager model.

From the little bit of data and description you provided, it seems you need the measure to be 0 for every date within a date range.  Assuming that's true, you'll want to define a query subject that returns all possible dates within all appropriate ranges.  Here it's OK to have extra rows.

This may be possible using the relatively new time intelligence features.  I don't use them yet, so I don't know.
Cognos may have a more elegant way to do this.  My solution here is brute force.

Once you have all possible dates for all cases in your data, join that to the rest of the data:

select <some fields>
, coalesce([Measure], 0) as Measure
, [date].[date]

from [date] d
  left outer join <the rest of your data> source on source.[date] = d.[date]

Now you should get

Date           Measure
11/01/2022     2
11/02/2022     4
11/03/2022     0
Average        2

You'll need to perform other filtering as well to limit the date range to match the source data.