topic Re: Summing only Max Values in set Analysis in QlikView

Summing only Max Values in set Analysis

Wed, 07 Dec 2016 10:00:39 GMT

Morning all.

I have a Set Analysis that works well when I filter the table as we only want to show the Max value for a certain Division or Site, My client now wants to include multiple Division's and want's to Sum all the max values I have tried

=SUM(aggr(MAX({$<

[Activity Conversion Key] ={'FloorArea'}

,[Activity Period Year]={$(vYearFilter)}

}

num([Activity Value],'#,##0.000'))

But it does not work.

I have attached an excel of the data

Re: Summing only Max Values in set Analysis

sunny_talwar — Wed, 07 Dec 2016 10:04:38 GMT

What are the chart dimensions where you are planning to use this expression?

Re: Summing only Max Values in set Analysis

Wed, 07 Dec 2016 10:08:44 GMT

The chart Dimension is year. It's going into a Bar chart with a Cyclic Group.

Re: Summing only Max Values in set Analysis

sunny_talwar — Wed, 07 Dec 2016 10:13:31 GMT

May be try like this:

=Sum(Aggr(Max({$<[Activity Conversion Key] = {'FloorArea'}, [Activity Period Year]={$(vYearFilter)}>} [Activity Value]), Year, Division, $(='[' & GetCurrentField(CycleGroupName) & ']')))

Where you have two dimensions in your chart -> Year and Cycle Group

Re: Summing only Max Values in set Analysis

Wed, 07 Dec 2016 10:16:53 GMT

Excellent. Thank you Sunny works a treat.