topic Need help on Sum the max value of drill down fields in QlikView

Need help on Sum the max value of drill down fields

elaineng — Fri, 15 Jul 2011 07:09:32 GMT

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

kaushiknsolanki — Fri, 15 Jul 2011 07:15:36 GMT

Hi,

Can you please describe the problem with example.

Regards,

Kaushik Solanki

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

Fri, 15 Jul 2011 07:17:35 GMT

Hi,

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

Still u hav any issues.

Let me knw

Regards,

Sri

Need help on Sum the max value of drill down fields

elaineng — Fri, 15 Jul 2011 07:41:44 GMT

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

Fri, 15 Jul 2011 08:22:19 GMT

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.

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

Miguel_Angel_Baeyens — Fri, 15 Jul 2011 08:31:13 GMT

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