help with Max function combining sum calculation

Tue, 23 Jun 2015 09:48:28 GMT

Hello

I have a dashboard showing cost of building maintenance. These cost are worked out by the maintenance cost of each asset within a building (these come from asset table). I can roll up costs (from assets table) to show the maintenance cost of each building quite easily and have done this within the analysis pages of my qlikview document.

However on the main dashboard screen, I need to show just the name of the most expensive building to maintain (not the cost as these are shown elsewhere)

I'm trying to use something like the maxString function to show the building name, but doing so based on the sum of the maintenance cost, to get the most expensive building.

The expression I've tried so far is =Maxstring({<'=sum(FinalTargetPrice2)'>} [Building Code])

but this only shows the maxString of buildings so the result it gives me the highest building name (from A-Z) and ignores the part where I'm trying to display the building name based on sum of FinalTargetPrice field.

Can anyone point me in direction that may help with this?

Thanks in advance

Dan

Re: help with Max function combining sum calculation

lironbaram — Tue, 23 Jun 2015 10:01:42 GMT

you can use something like

=FirstSortedValue([Building Code],-aggr(sum(FinalTargetPrice2),[Building Code]))

Re: help with Max function combining sum calculation

Tue, 23 Jun 2015 10:08:42 GMT

Great that worked Thanks Liron

topic Re: help with Max function combining sum calculation in QlikView

help with Max function combining sum calculation

Re: help with Max function combining sum calculation

Re: help with Max function combining sum calculation