Hi,

I working on a calculation which needs average YTD calculation for a measure based on period. Please suggest.
Env: Cognos 11.1.7

You cannot view this attachment.

The scenario is that when data is available for both periods, YTD average property is working fine. For YTD, we have filter defined from relative table for period type(YTD) and measure gets calculated. But this measure has to perform the Average of YTD.

When data is NULL for a period(Nov - 202311), expected YTD average should be from 202312(December) period and it should be 45, but instead it shows 22.5. 

Below approach works for YTD average when data is present for both periods, but gives issue when measure is NULL for a period:
Query1(base query) uses measure from package and defined with coalesce function.  Query 2 is a union query pulling these columns into query which also has other base queries(diff fact tables).  Query 3 is final query where i have defined if-else condition to handle NULL. (-> if measure is NULL then 0 else measure)

I don't have Cognos in front of me but giving you the idea. Count the number of Periods that have the data and then divide the total by that.

Something like - count ( distinct case when total[Measure] for [Period] > 0 then [Period] )

Quote from: davidcognos on 19 Apr 2024 05:53:14 PMHi,

I working on a calculation which needs average YTD calculation for a measure based on period. Please suggest.
Env: Cognos 11.1.7

You cannot view this attachment.

The scenario is that when data is available for both periods, YTD average property is working fine. For YTD, we have filter defined from relative table for period type(YTD) and measure gets calculated. But this measure has to perform the Average of YTD.

When data is NULL for a period(Nov - 202311), expected YTD average should be from 202312(December) period and it should be 45, but instead it shows 22.5. 

Below approach works for YTD average when data is present for both periods, but gives issue when measure is NULL for a period:
Query1(base query) uses measure from package and defined with coalesce function.  Query 2 is a union query pulling these columns into query which also has other base queries(diff fact tables).  Query 3 is final query where i have defined if-else condition to handle NULL. (-> if measure is NULL then 0 else measure)

Hi,

Averages in Cognos usually ignore null values, so if you were just using a simple average summary (or function in a calculation) you would get the desired result. The issue looks to be that you have defined an if-then-else expression that converts the null to a zero - if ([measure] is NULL) then (0) else ([measure])
An Average will always take account of zero values, so it is skewing your results here. Just leave the measure as null, and you should be fine.

Cheers!

MF.