SAP Knowledge Base Article - Public

2960434 - Conditional aggregation applied to operator FIRST QUARTILE & MEDIAN & THIRD QUARTILE is not calculated correctly in SAP Analytics Cloud (SAC)

Symptom

  • Conditional aggregation applied to operator FIRST QUARTILE & MEDIAN & THIRD QUARTILE  is not calculated correctly in SAP Analytics Cloud (SAC)
  • The 3 operators are working fine witout using Conditional aggregation
  • Other operators like MAX, SUM can get correct result with same conditional aggregation

Environment

  • SAP Analytics Cloud (Enterprise) 2020.17.2

Reproducing the Issue

  1. Log on to SAC tenant.
  2. Create a story by importing data from a file containing the following data:
    --------------
    REGION,SALES
    A,10
    B,22
    C,36
    D,44
    E,58
    F,60
    G,74
    H,82
    I,98
    --------------
  3. Add a Table to show measure [SALES] only.
  4. Add the following Aggregation Calculation:
    • FQ:
      • Operation =  FIRST QUARTILE
      • Measure = SALES
      • Aggregation Dimension =  REGION
      • Use conditional aggregation=Checked
      • Do not have Measure values for Conditions: Region in {I, H}
    • ME:
      • Operation =  FIRST QUARTILE
      • Measure = SALES
      • Aggregation Dimension =  REGION
      • Use conditional aggregation=Checked
      • Do not have Measure values for Conditions: Region in {I, H}
    • TQ:
      • Operation =  THIRD QUARTILE
      • Measure = SALES
      • Aggregation Dimension =  REGION
      • Use conditional aggregation=Checked
      • Do not have Measure values for Conditions: Region in {I, H}
  5. Get the wrong result of the 3 Aggregation Calculations as below:
    • FQ=5 (22 is expected: 25% of the data is less than this value )
    • ME=36 (44 is expected: half of the data lies below the median value, and half lies above )
    • TQ=59 (60 is expected: 75% of the data is less than this value ) 

Cause

  • This is By-Design.
  • While creating aggregation calculation, with conditional aggregation selected and “Do not have Measure values for Conditions” excluded with Region in {I, H}, the backend request that is created with the region row values to 'null'. Hence, calculation considers these rows also.
  • For example, with conditional aggregation and restrictions on region {H, I}, the data would become following and the result would be 36.
    --------------
    REGION SALES
    H null
    I null
    A 10
    B 22
    C 36
    D 44
    E 58
    F 60
    G 74
    --------------

Resolution

  • Use MEDIAN excl. NULL option instead of MEDIAN (same of others), if you are using conditional aggregation with restrictions, which will give the proper results.

See Also

Your feedback is important to help us improve our knowledge base.

Keywords

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,Error, Issue, System, Data, User, Unable, Access, Connection, Sac, Connector, Live, Acquisition, Up, Set, setup, Model, BW, Connect, Story, Tenant, Import, Failed, Using, Working, SAML, SSO, sapanalyticscloud, sap analytical cloud, sap analytical cloud, SAC, sap analyst cloud, connected, failure, stopped, aggregation, calculation, Conditional, FIRST QUARTILE, MEDIAN, THIRD QUARTILE, Condition , KBA , LOD-ANA-DES , Model, Story Design & Visualizations , Problem

Product

SAP Analytics Cloud 1.0