5 Replies Latest reply: Jul 15, 2011 4:31 AM by Miguel Angel Baeyens de Arce

# Need help on Sum the max value of drill down fields

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

• ###### Need help on Sum the max value of drill down fields

Hi,

Can you please describe the problem with example.

Regards,

Kaushik Solanki

• ###### Need help on Sum the max value of drill down fields

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

• ###### Re: Need help on Sum the max value of drill down fields

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

• ###### Re: Need help on Sum the max value of drill down fields

Hi,

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

Still u hav any issues.

Let me knw

Regards,

Sri

• ###### Re: Need help on Sum the max value of drill down fields

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.