Cognos Analytics

 View Only
  • 1.  Calculate YTD Average handling for NULL data

    Posted Fri April 19, 2024 07:01 PM

    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

  • 2.  RE: Calculate YTD Average handling for NULL data
    Best Answer

    IBM Champion
    Posted 30 days ago


    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