SAP Knowledge Base Article - Public

2625275 - How do I create an Average that ignores NULLs and 0s in SAP Analytics Cloud (SAC)?

Symptom

How do I create an Average that ignores NULLs and 0s in SAP Analytics Cloud (SAC)?

Environment

  • SAP Analytics Cloud 2018.4.1

Resolution

  1. Open your SAP Analytics Cloud Model (Browse > Models)
  2. Click "Account"
  3. Add a new row to account:
    • ID=MyAverage
    • Description=MyAverage
    • AccountType=NFIN
    • Exception Aggregation Type=AVG excl. 0, NULL
    • Exception Aggregation Dimension=Dimension(s) Over Which Average Is Valid
    • Formula=[Account/Measure To Average]
  4. Save Model
  5. Use MyAverage in your Story

The above resolution applies for Planning and Analytic Models but for Live Data Models (Remote HANA Views) your HANA system must have at least the version of 1.0.122.14.00000000.

See Also

Your feedback is important to help us improve our knowledge base.
Please rate how useful you found this article by using the star rating feature at the beginning of this article.
Thank you.

Keywords

EPM, SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, EPM-ODS, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics, average, zero, null, 0, blank, exclude , KBA , LOD-ANA , SAP Analytics Cloud , LOD-ANA-BI , SAP Analytics Cloud - Business Intelligence (BOC) , LOD-ANA-PL , SAP Analytics Cloud – Planning (BOC) , LOD-ANA-BR , SAP Analytics Cloud - Digital Boardroom , LOD-ANA-PR , SAP Analytics Cloud – Predictive (BOC) , How To

Product

SAP Analytics Cloud 1.0