Sum of values higher than rank 20 of a dimension

[TPoismans]

Hi all

Consider the following data:

Company	Person	Date	Timestamp	Value
A	X	1/2/2019	1/2/2019 7:00:00	3594
A	X	1/2/2019	1/2/2019 7:05:00	1263
A	X	1/2/2019	1/2/2019 7:10:00	1961
A	X	1/2/2019	1/2/2019 7:15:00	2002
A	X	1/2/2019	1/2/2019 7:20:00	1653
A	X	1/2/2019	1/2/2019 7:25:00	3595
A	X	1/2/2019	1/2/2019 7:30:00	3141
A	X	1/2/2019	1/2/2019 7:35:00	1726
A	X	1/2/2019	1/2/2019 7:40:00	3233
A	X	1/2/2019	1/2/2019 7:45:00	3531
A	X	1/2/2019	1/2/2019 7:50:00	3890
A	X	1/2/2019	1/2/2019 7:55:00	1710
A	X	1/2/2019	1/2/2019 8:00:00	1665
A	X	1/2/2019	1/2/2019 8:05:00	4126
A	X	1/2/2019	1/2/2019 8:10:00	4334
A	X	1/2/2019	1/2/2019 8:15:00	2909
A	X	1/2/2019	1/2/2019 8:20:00	3226
A	X	1/2/2019	1/2/2019 8:25:00	3401
A	X	1/2/2019	1/2/2019 8:30:00	4244
A	X	1/2/2019	1/2/2019 8:35:00	4111
A	X	1/2/2019	1/2/2019 8:40:00	1341
A	X	1/2/2019	1/2/2019 8:45:00	1888
A	X	1/2/2019	1/2/2019 8:50:00	1989

What I want to achieve is the following:

Company	Person	Date	# lines	Value of lines > 20
A	X	1/2/2019	23	5218

The last two colums are a measure. The second measure is what I'm looking for.

If there are more than 20 lines for a person on 1 date, give me the sum of the values of the lines above 20. For the dummy data, that would be: 1341+1888+1989 = 5218.

If any more info is needed, please ask and thanks in advance.
Kind regards,

Tim P.

[sunny_talwar]

If your data is sorted correctly by the ascending order by timestamp in the script, then you can try this

If(Count(Timestamp) > 20, Sum(Aggr(If(RowNo() > 20, Value), Company, Person, Date, Timestamp)))

[TPoismans]

The value is already a calculated value with set analysis. So RowNo() sadly won't work. 
Should've mentioned that, my bad.

Regards,
Tim P.

[sunny_talwar]

What is the expression for Value? It might still work...

[TPoismans]

The expression for the value is:

-Sum({$<[Tankpost Soort]={"Leveren","Ledigen","Bunkeren"},[Tankpost Aantal Check]={1}>} [Tankpost Aantal])

But that won't help much if can't try it with real data, which I'm not allowed to hand out.


Kind regards,
Tim P.

[sunny_talwar · Accepted solution]

That is true, but can you try this out?

If(Count(Timestamp) > 20, Sum(Aggr(If(RowNo() > 20,
-Sum({$<[Tankpost Soort]={"Leveren","Ledigen","Bunkeren"},[Tankpost Aantal Check]={1}>} [Tankpost Aantal])
), Company, Person, Date, Timestamp)))

If this doesn't work and you are using QlikView 12 or above, you can try this

If(Count(Timestamp) > 20, Sum(Aggr(If(RowNo() > 20,
-Sum({$<[Tankpost Soort]={"Leveren","Ledigen","Bunkeren"},[Tankpost Aantal Check]={1}>} [Tankpost Aantal])
), Company, Person, Date, (Timestamp, (NUMERIC)))))

[TPoismans]

First measure worked perfectly, thanks!