Hi,
sounds realistic to me.
average is a calculation that computes sum(measure) / count(measure). Null is not an instance of an element/measure and won't be counted.
40+45 / 2 = 42.5
45 / 1 = 45
You have written that you handel NULL values with the coalesce function and replace NULL with 0. That is whole total different story as 0 is an instance of an element end therefore will be counted. This will results into 45+0 / 2 = 22.5. Can you check on which elements/calculations your average() is based? it should be the one without any coalesce function.
------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen
------------------------------
Original Message:
Sent: Fri April 19, 2024 07:00 PM
From: Nagaraju J
Subject: Calculate YTD Average handling for NULL data
Hi,
We are working on a calculation which needs average YTD calculation for a measure based on period. Please suggest.
Env: Cognos 11.1.7
The scenario is that when data is available for both periods, YTD average 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)
------------------------------
Nagaraju Janmanchi
------------------------------