0 Replies Latest reply: Jan 13, 2016 11:00 AM by Alejandro Barcos RSS

    Aggregating by condition

      Two weeks ago I create a discussion about a aggregation for a dimension not included in fact table.

       

      Today, I am searching a similar solution for a dataset with more complexity.

       

      I have a Fact Table that concatenating three different data sources.

       

      Using the same example from my previous post:

       

      Fact Table:

         

      FACT TABLE
      Id_PersonSpecialtyAgeGenderCostAffected
      1Pediatrics3Male10001
      2 1Female 1
      3Emergencies14Male20001
      4 33Male 1
      5Gynecology20Female30001
      6 45Male 1
      7Oncology67Male60001
      8Geriatrics74Female5001

       

      Fact table contain a concatenation of 3 differents data sources. Only one of them has Specialty (and Cost) information.

       

      Dimension:

         

      DIMENSION TABLE Specialty
      DimensionCondition
      Pediatrics<15
      Gynecology>15 & Female
      Geriatrics>65
      OncologyALL
      EmergenciesALL

       

      I need a table with two expression that calculates Cost by Specialty and Affected People by Specialty (It is not a count of fact table, it is a record counter filtered by condition). So:   

         

      Expected
      SpecialtySum(Cost)Sum(Affected)
      Pediatrics10003
      Emergencies20008
      Gynecology30002
      Oncology60008
      Geriatrics5002
      Pediatrics30008
      Emergencies
      Pediatrics40005 (3+2)
      Gynecology
      Oncology65008
      Geriatrics
      Pediatrics45006 (3+2+2-1, this one is for a woman in Gyn & Ger)
      Gynecology
      Geriatrics

       

      I try to get this result with a Sum(Cost) and pick(match(Affected)) but pick & match doesn't work fine in a table with Specialty.

       

      How can I get these two expression correctly?

       

      Thank you in advance.