What's the aggr dimension value for the highest value of expression

[#1]

Thanks to some earlier help from Daniel Rozental I have the following expression which gives me the highest (max) value of sum(SalesAmount) over the last 12 periods or [$Year Month]s if ploted on a chart. with [$Year Month] as the dimension.

I now want to know which [$Year Month] this max value corresponds to?

=max(
{$<PeriodCounter = {">$(#=max(PeriodCounter-12))<=$(#=max(PeriodCounter))"},[$Year Month]= >} 
     aggr(
          sum({$<PeriodCounter = {">$(#=max(PeriodCounter-12))<=$(#=max(PeriodCounter))"},[$Year Month]= >} 
               [SalesAmount]
                )
     ,[$Year Month])
 )

Month [SalesAmount]
2011M07 £50841
2011M06 £5364840 - this is the highest value - so I need the anwer 2011M06
2011M05 £5193593
2011M04 £3813374
2011M03 £4220276
2011M02 £4163247
2011M01 £3002792
2010M12 £5242737
2010M11 £4680143
2010M10 £3362565
2010M09 £5057832
2010M08 £5060385

Any help much appreciated
Regards
Matthew

[swuehl]

Hi,

I think Miguel suggestion using

FirstSortedValue(Month, -SalesAmount)

should work,

please note that sort order should return a numerical value, if your Sales Amount is a text with leading pound symbol, you may try:

FirstSortedValue(Month, -num#(SalesAmount,'FORMAT'))

Where format is appropriate format string (I can't find pound symbol on my keyboard at the moment).

Regards,

Stefan

Nachricht geändert durch swuehl - typos

[danielrozental]

FirstSortedValue should work assuming that he has a single record for SalesAmount for each month and that there won't be two months with the exact same SalesAmount (which is highly improbable either way).

BTW, just tested the previous expression in the sample attached and it works fine.

[#4]

Amazing – that works – thanks Daniel

I’ll have to study it now to see what you’ve done – looks rather Jedi Knight at the moment

Matthew Stephen

Solution Consultant UKI

+447557267135

qlik.com<http://www.qlik.com/>

NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited. QlikTech UK Limited, registered in England and Wales with number 5292408, and whose registered office is Villiers House, Clarendon Avenue, Leamington Spa, Warwickshire, CV32 5PR.

[tabletuner]

This is a good solution for my problem as well. However, it is very memory intesive. Isnt there a solution that is more efficient?