topic Re: What's the aggr dimension value for the highest value of expression in QlikView

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

Tue, 26 Jul 2011 11:26:25 GMT

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

Miguel_Angel_Baeyens — Tue, 26 Jul 2011 11:58:05 GMT

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

ToniKautto — Tue, 26 Jul 2011 12:12:23 GMT

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

Tue, 26 Jul 2011 12:25:10 GMT

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

Tue, 26 Jul 2011 13:31:51 GMT

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/>

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

danielrozental — Tue, 26 Jul 2011 13:44:20 GMT

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

Tue, 26 Jul 2011 14:51:09 GMT

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/>

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

danielrozental — Tue, 26 Jul 2011 14:54:54 GMT

Can you post a sample qvw?

Sorry, It won't work.

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

Tue, 26 Jul 2011 15:06:47 GMT

Here it is

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

danielrozental — Tue, 26 Jul 2011 15:07:13 GMT

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

swuehl — Tue, 26 Jul 2011 15:07:36 GMT

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

danielrozental — Tue, 26 Jul 2011 15:11:18 GMT

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

Tue, 26 Jul 2011 17:46:35 GMT

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/>

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

tabletuner — Thu, 14 Jun 2012 13:34:53 GMT

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