13 Replies Latest reply: Jun 14, 2012 9:34 AM by Tjeerd Wieberdink

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

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

• ###### Re: What's the aggr dimension value for the highest value of expression

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

• ###### Re: What's the aggr dimension value for the highest value of expression

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.

• ###### Re: What's the aggr dimension value for the highest value of expression

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

• ###### Re: What's the aggr dimension value for the highest value of expression

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.

• ###### Re: What's the aggr dimension value for the highest value of expression

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

• ###### Re: What's the aggr dimension value for the highest value of expression

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.

• ###### Re: What's the aggr dimension value for the highest value of expression

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]))

```
• ###### Re: What's the aggr dimension value for the highest value of expression

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.

• ###### Re: What's the aggr dimension value for the highest value of expression

Can you post a sample qvw?

Sorry, It won't work.

• ###### Re: What's the aggr dimension value for the highest value of expression

Here it is

• ###### Re: What's the aggr dimension value for the highest value of expression

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]))

```
• ###### Re: What's the aggr dimension value for the highest value of expression

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.

• ###### Re: What's the aggr dimension value for the highest value of expression

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