Hello,
how can I calculate the avg of a measure with missing data?
| Measure |
| 11/01/2022 | 2 |
| 11/02/2022 | 4 |
| 11/03/2022 | |
| 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.