topic Re: Max value in Set analyse ... in QlikView

Max value in Set analyse ...

jozisvk11 — Wed, 25 Nov 2020 16:16:04 GMT

Sum({< [Account] = {'326100'}, [Type] = {'5'} , Max(Rank of the row)>}Round([Sum of money],0.01))), 0.01)

At first, I have table :

ID Rank of the row Type Sum of money

K01 1 5 100

K01 2 5 200

K01 3 5 700

K02 1 5 300

K03 1 5 500

K04 1 5 550

K04 2 5 650

...

I need filter according to Max(Rank of the row) But max function don´t run.

My result should be :

ID Rank of the row Type Sum of money

K01 3 5 700

K02 1 5 300

K03 1 5 500

K04 2 5 650

Thanks for helping.

Re: Max value in Set analyse ...

swuehl — Fri, 17 Jun 2016 09:23:41 GMT

Rank of the row is a field?

Try

FirstSortedValue({< [Account] = {'326100'}, [Type] = {'5'} >} Round([Sum of money],0.01), -[Rank of the row])

Re: Max value in Set analyse ...

Kushal_Chawda — Fri, 17 Jun 2016 09:27:46 GMT

Sum({< [Account] = {'326100'}, [Type] = {'5'} ,[Rank of the row] ={"=aggr(max([Rank of the row]),ID)"}>}Round([Sum of money],0.01))

Re: Max value in Set analyse ...

sunny_talwar — Fri, 17 Jun 2016 09:32:41 GMT

Do you think this will work? I have a feeling that it might not.

May be this:

Sum({< [Account] = {'326100'}, [Type] = {'5'}, ID ={"=[Rank of the row] = Aggr(Max(TOTAL <ID> [Rank of the row]), ID, [Rank of the row])"}>} Round([Sum of money],0.01))

Re: Max value in Set analyse ...

Kushal_Chawda — Fri, 17 Jun 2016 09:35:16 GMT

I had same feeling but somewhere I had seen this kind of expression,so let's try

Re: Max value in Set analyse ...

sunny_talwar — Fri, 17 Jun 2016 09:37:31 GMT

I guess the best way would be to test it out. I will be back with my findings based on this sample at least

Re: Max value in Set analyse ...

swuehl — Fri, 17 Jun 2016 09:40:40 GMT

to add to my previous answer, you need to use a table with dimensions ID and Type

and two expressions:

=Max({< [Account] = {'326100'}, [Type] = {'5'} >} [Rank of the row])

=FirstSortedValue({< [Account] = {'326100'}, [Type] = {'5'} >} Round([Sum of money],0.01), -[Rank of the row])

Re: Max value in Set analyse ...

Kushal_Chawda — Fri, 17 Jun 2016 09:42:48 GMT

Yes. I don't have QV to test.. adha kaam mera hava me hota he

Re: Max value in Set analyse ...

nizamsha — Fri, 17 Jun 2016 10:02:30 GMT

not the best way but u can use this it will give the op what u want

Dimension: =Aggr(MAx([Rank of the row]),ID) and also add the remaining dimension column

expression : =MAx(Aggr(max([Sum of money]),ID))

Re: Max value in Set analyse ...

sunny_talwar — Fri, 17 Jun 2016 10:09:01 GMT

Both the methods are not working. Although one way would be to create a flag in the script

Table:

LOAD * Inline [

ID, Rank of the row, Type, Sum of money

K01, 1, 5, 100

K01, 2, 5, 200

K01, 3, 5, 700

K02, 1, 5, 300

K03, 1, 5, 500

K04, 1, 5, 550

K04, 2, 5, 650

];

Join (Table)

LOAD ID,

Max([Rank of the row]) as [Rank of the row],

1 as Flag

Resident Table

Group By ID;

And then using the flag:

=Sum({<Flag = {1}>}[Sum of money])

Using Flag is easy and efficient, but makes the expression Static, So not sure if you would want that.

Another Alternative would be to create a Key for ID and Rank of the row field.

Table:

LOAD *,

AutoNumber(ID&[Rank of the row]) as Key;

LOAD * Inline [

ID, Rank of the row, Type, Sum of money

K01, 1, 5, 100

K01, 2, 5, 200

K01, 3, 5, 700

K02, 1, 5, 300

K03, 1, 5, 500

K04, 1, 5, 550

K04, 2, 5, 650

];

And then my initial idea will work:

Sum({<Key ={"=[Rank of the row] = Aggr(Max(TOTAL <ID> [Rank of the row]), ID, [Rank of the row])"}>} Round([Sum of money],0.01))

But what is the point of doing all this, if Stefan's expression using FirstSortedValue() is doing exactly what might be needed

Attaching the qvw with these options attached.

Best,

Sunny

Re: Max value in Set analyse ...

Kushal_Chawda — Fri, 17 Jun 2016 10:12:30 GMT

Our job is to give options, that is what we are doing. But we should not try it when Stefan has already replied because his solution is always optmimal

Re: Max value in Set analyse ...

sunny_talwar — Fri, 17 Jun 2016 10:18:22 GMT

Well I wouldn't stop trying. Unless we won't try, we won't improve. We should all try to be better than Stefan one day . Its a different matter if we will reach to that level

Re: Max value in Set analyse ...

Kushal_Chawda — Fri, 17 Jun 2016 10:23:49 GMT

You are correct. tune aankhe khol di meri.

Re: Max value in Set analyse ...

jozisvk11 — Mon, 20 Jun 2016 08:08:48 GMT

Thanks a lot for helping me.