Max, sum aggregate

[#1]

Hi All,

I have a database with Area, Client No., Date & Value. A client can appear several times in the database.I would like to sum up all the Max(values) and display the data with Area as the dimension; effectively QlikView will be summing up the largest number at client level. I can't use the lastsortvalue because I have more than one transaction per day per client in some instances.

Thank you

H

[preminqlik]

noconcatenate

FINAL:

Load Area,

clientNo,

Date,

max(value)               as                    maxValue

resident orginal group by

Area,

clientNo,

Date;

drop table orginal;

>>in front end take Chart>dimension Area >> exp as  sum(maxValue)

[anbu1984]

Select

  Area,

  ClientNo,

  Sum(Max(Value))

From Table

Group by

  Area,

  ClientNo

[#4 · Accepted solution]

Thanks for the reply Prem,

I have decided to create a separate "value" mini table, as I don't want to drop the original table because the data as it was was perfect for other bits of analysis. Into the new table I pulled in distinct Client No and then Max(Value) grouped by ClientNo.

Many Thanks

H