topic Re: Min/Max aggregated function in QlikView

Min/Max aggregated function

Thu, 28 Aug 2014 10:12:43 GMT

Hi all,

I'm trying to find max, min values for netted amounts throughout the day.

For every hour I'm calculating the net value for incoming and outgoing payments, leaving a negative or positive value.

Now I wish to rank these netted flows, for instance the largest positive and negative hourly flow for the selected date.

I tried the below, the syntax seems to be OK, but I'm only getting null values in return.

any ideas?

MAX (AGGR (SUM ( {<Receiver = {'US'}>} [Amount] )- sum( {<Receiver -= {'US'}>} [Amount] ), Time ) )

Thanks in advance,

Olle

Re: Min/Max aggregated function

PrashantSangle — Thu, 28 Aug 2014 10:16:55 GMT

Hi,

Try something like this,

MAX (AGGR (SUM ({<Receiver = {'US'}>} [Amount]),Time)) - Max(AGGR (sum( {<Receiver -= {'US'}>} [Amount]),Time))

Regards,

Re: Min/Max aggregated function

MK_QSL — Thu, 28 Aug 2014 10:26:23 GMT

Max(Aggr(Max({<Receiver = {US'}>}Amount)-Max({<Receiver -= {US'}>}Amount),Time))

Re: Min/Max aggregated function

Thu, 28 Aug 2014 10:56:09 GMT

Hi Max,

tried it and it's giving me the very same results as the below "core" calculation.

sum( {<Receiver = {'US'}>} [Amount] )- sum( {<Receiver -= {'US'}>} [Amount] ))

@MAnish,

receiving null values with your code.

//O

Re: Min/Max aggregated function

PrashantSangle — Thu, 28 Aug 2014 10:58:16 GMT

Hi,

Can you post sample application.

Regards,

Re: Min/Max aggregated function

Thu, 28 Aug 2014 11:08:08 GMT

Pls see attached Sample File, I've replaced counterpart names to "THEM" so no need for the -=US.

//O

Re: Min/Max aggregated function

jonathandienst — Thu, 28 Aug 2014 11:10:58 GMT

Are you using this in a chart/table with the date as a dimension? If so, you need to include this dimension in your Aggr() function. Like this (assuming the date is a field called [Date]):

Max(Aggr(Sum({<Receiver = {'US'}>} [Amount]) - Sum({<Receiver -= {'US'}>} [Amount]), [Time], [Date]))

HTH

Jonathan

Re: Min/Max aggregated function

Thu, 28 Aug 2014 11:42:14 GMT

Thanks Jonathan!