Aggregating the Values Dimension Totals Wise

[umartareen]

Hi,

How can we calculate the Values using the totals of second dimensions :

Example :

The expression columns in the above Pivot are

Value = Sum(Value)

Sum TOTAL Value = Sum(Value) / Sum(TOTAL Value)

The above "Sum Total Value" expression is calculating as per the Total value of Employee Dimension,

Example, For State_Id 1, it would be : 100 / 3100 = 0.03

              For State_Id 2, it is : 400 / 3100 = 0.13

But what is required is that the values must be calculated as per Totals of State_Id

ie, State_Id 1 in "Sum TOTAL Value" should be 100 / 500 = 0.2

     State_Id 2 in "Sum TOTAL Value" should be be 400 / 500 = 0.8.

Also, PFA of the Test Application

Any suggestions would be greatly appreciated.

Thanks

Umar

[sunny_talwar · Accepted solution]

Try this:

Sum(Value1)/Sum(TOTAL <Employee> Value1)

You basically need to tell the TOTAL qualifier about the field where you want to total on. By just using the TOTAL, it was doing the overall total. When I add <Employee>, it started doing total per Employee

[umartareen]

Works Perfect !

Thank You .

[umartareen]

Hi,

How can this be achieved while using set expressions ?

Lets take an example :

Sum({<Year, Month>} Value1) / Sum(TOTAL {<Year, Month>} Value1)

Thanks,

Umar

[sunny_talwar]

This:

Sum({<Year, Month>}Value1)/Sum(TOTAL <Employee> {<Year, Month>} Value1)

[umartareen]

Solved it !

Sum(<Employee> {<Year, Month>} Value1) / Sum(TOTAL <Employee> {<Year, Month>} Value1)

Regards,

Umar

[sunny_talwar]

umar khan wrote:

Solved it !

Sum(<Employee> {<Year, Month>} Value1) / Sum(TOTAL <Employee> {<Year, Month>} Value1)

Regards,

Umar

This isn't right, are you getting the right results? Should be this

Sum({<Year, Month>}Value1)/Sum(TOTAL <Employee> {<Year, Month>} Value1)

Without <Employee> in the numerator

[davyqliks]

This is awesome, thanks... didnt realise i could use a pivot and sub totals like this.

Nice work, thanks

Daniel