Need help on Sum the max value of drill down fields

[elaineng]

I'm having some issue to get the Sum of the max value of some drill down field

Enclosed the simplified version of the data structure, appreciate if anyone could help me to get the total value of the max amont on Region

[kaushiknsolanki]

Hi,

   Can you please describe the problem with example.

Regards,

Kaushik Solanki 

[#3 · Accepted solution]

Hi,

Please find the attachmetns. its possible in cyclic grouping. Hope it helps u.

Still u hav any issues.

Let me knw

Regards,

Sri

[elaineng]

Let me explain:

I have a table data of :

Region	Contry	Year	Value
Asia	M'sia	2011	1000
Asia	S'pore	2011	1200
Asia	M'sia	2010	2000
Asia	S'pore	2010	1100

I need to get the Max value of each Country and total it at Region level

In this case, the max value for M'sia is 2,000 and S'pore is 1,200, the total for Region should be 3,200

[#5]

Hi!

I got the solution that you need, but I used some scripting, basically you need grouping by country - after you use distint valuzes in Diagram, and the values are added correctly according to the dimension of the Diagram.

[Miguel_Angel_Baeyens]

Hi,

Assuming you have a straight chart with Region, Country, Year and Value as dimensions (in that order), the following  expressions should do the trick for the max per region and country

If(Aggr(Max(TOTAL <Region, Country> Value), Region, Country, Year) = Value, Value)

And the sum of those values

Sum(If(Aggr(Max(TOTAL <Region, Country> Value), Region, Country, Year) = Value, Value))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica