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

# 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_Person Age Gender Affected 1 3 Male 1 2 1 Female 1 3 14 Male 1 4 33 Male 1 5 20 Female 1 6 45 Male 1 7 67 Male 1 8 74 Female 1

Dimension:

 DIMENSION TABLE Dimension Sum (Affected) Condition Dim1 3 <15 Dim2 2 >15 & Female Dim3 2 >65 Dim4 8 ALL Dim5 8 ALL … … … Total 8

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 Dimension Sum(Affected) Dim1;Dim2 5 Dim2;Dim3 3 Dim1;Dim2;Dim3 6 Dim1;Dim2;Dim4 8 Nothing in Selector 8

I try to get this result:

 Expedted Dimension Cost Affected people Total 6000 8 Dim1 1000 3 Dim2 1000 2 Dim3 2000 2 Dim4 1500 8 Dim5 500 8

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 Dimension Cost Affected people Total 6000 8 Dim1 1000 0 Dim2 1000 0 Dim3 2000 0 Dim4 1500 0 Dim5 500 0

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?

• ###### Re: Aggregating from different level

Perhaps the attached example helps. If not, please explain why.

• ###### Re: Aggregating from different level

Many thaks Gysbert!

It is not a perfect solution, because Totals do not work correctly, but for me it is fine.

Now, I have a problem with my data because Fact Table is concatenating some local tables and expression don't work fine because a table has checked dimension. Anyway, it's another issue.