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

[Miguel_Angel_Baeyens]

Hello Matthew,

This should work

FirstSortedValue(Month, -SalesAmount)

That is read as "return the first value ordered by SalesAmount, descending".

Is that waht you are looking for?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[ToniKautto]

You could probably use Only() which will return the distinct value, that will be based on the aggregated total max in your data. With your additional limitaitons from above, but by the following principle;

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

Note; Might miss some syntax, since I did not have the script editor available to validate it

[#4]

Thanks Miguel – but I’ve tried a variety of FirstSortedValue(Month, -SalesAmount) with no luck

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.

[#5]

Thanks Tony but no joy – I’d like to return the value to a text object, in the same way the function below does – the Only() function appears to be only available for chart expressions – but even then I can’t seem to get any joy

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.

[danielrozental]

Try this

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

[#7]

Thanks Daniel – but this time no joy

It returns -, the syntax appears to be fine

I broke it down a little

=maxstring(aggr(

if(5364840.1799999 =

max(total

{$}

)

,[$Year Month])

)

, )

, ))

Still returns –

As does

=maxstring(aggr(

if(sum({$}

)

=

5364840.1799999

, )

, ))

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.

[danielrozental]

Can you post a sample qvw?

Sorry, It won't work.

[#9]

Here it is

[danielrozental · Accepted solution]

Got it now, try this

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