2 Replies Latest reply: Jan 7, 2016 9:54 AM by Alejandro Barcos RSS

    Aggregating from different level

      Hi all,

       

      I have an issue with a Sum of diferent values for a dimension that is a lower level respect fact table.

                                                                   

      My fact table don't include a dimension that I need for work with:

       

      FACT TABLE
      Id_PersonAgeGenderAffected
      13Male1
      21Female1
      314Male1
      433Male1
      520Female1
      645Male1
      767Male1
      874Female1

       

      Dimension:

       

         

      DIMENSION TABLE
      DimensionSum (Affected)Condition
      Dim13<15
      Dim22>15 & Female
      Dim32>65
      Dim48ALL
      Dim58ALL
      Total8

         

      Although 'Dimension' is not included in Fact Table, it is possible work with it joining this dimension by different conditions like Age and Gender. I need obtain something like this, but additionally I need work with multiple values for this dimension:

       

      Expressions
      Selected DimensionSum(Affected)
      Dim1;Dim25
      Dim2;Dim33
      Dim1;Dim2;Dim36
      Dim1;Dim2;Dim48
      Nothing in Selector8

       

      I try to get this result:

         

      Expedted
      DimensionCostAffected people
      Total60008
      Dim110003
      Dim210002
      Dim320002
      Dim415008
      Dim55008

       

      with a Set Analisys like:

       

      Sum({<$(PARAM Excluded Dimensions)  >} $(PARAM Conditions) FT_AFFECTED_PEOPLE))

       

      where:

       

      $(PARAM Excluded Dimensions) are Dimensions including 'Dimension'. I need this clause for obteining the correct value.

      $(PARAM Conditions) is a CASE expression that add filtering condition for fact.

      FT_AFFECTED_PEOPLE is a simple registrer count from Fact Table.

       

      With this solution I can obtain the Total Count of affected people but don't get the value of each dimension:

         

      Solution1
      DimensionCostAffected people
      Total60008
      Dim110000
      Dim210000
      Dim320000
      Dim415000
      Dim55000

       

       

       

       

      This solution works fine for a text box with a totalizer, but in a Pivot Table with 'Dimension' is not a fine solution.

       

      Other solution that I tried are Aggregator by 'Dimension' and modify script for get a field with a value for each value of 'Dimension', but  I can't get a correct solution.

       

      What can I do for get correct solution avoiding cross join between Dimension and Fact Table?

       

      Thank you in advance.