1 Reply Latest reply on Feb 4, 2019 4:07 AM by Jim Dehner

    Aggregate Function with NULLs - A Better Way?

    Shane Carr

      Hello,

       

      I have a data related to (for example) a Dentists office.

       

      There are 2 KPIs I'm interested in tracking and accumulating Year to Date (YTD) - for now though I've just calculated the full year 2018

       

      The data are as follows:

         

      DescriptionKPICodeJanFebMarAprMayJuneJulyAugSeptOctNovDec
      Existing Clients SeenKPI01174184162166189179122167194NULL253185
      New Clients SeenKPI02194029263645396450NULL4032

       

      To calculate the full year I have used the following calculated measure:

      SUM(IIF([KPICode] = "KPI01", zn([Jan])+zn([Feb])+zn([Mar])+zn([Apr])+zn([May])+zn([June])+zn([July])+zn([Aug])+zn([Sept])+zn([Oct])+zn([Nov])+zn([Dec]),0))

      + SUM(IIF([KPICode] = "KPI02",zn([Jan])+zn([Feb])+zn([Mar])+zn([Apr])+zn([May])+zn([June])+zn([July])+zn([Aug])+zn([Sept])+zn([Oct])+zn([Nov])+zn([Dec]),0))

       

      What would be a more elegant way of doing this same calculation?

      Thanks for your help.