topic How to get the last value from a field without having selection on it in QlikView

How to get the last value from a field without having selection on it

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

I have a field named 'item' which is having data from 1 to 1001. i want to create two variable which will give me 1001 and 1000 as the output(last two values from item). currently I am using the expressions SubField(concat(aggr(only({<item=p(item)>}item),item),'|'),'|',-1)

and SubField(concat(aggr(only({<item=p(item)>}item),item),'|'),'|',-2) as the two variable expressions. I am supposed to get the answers 1000 and 1001. but I am getting 999 and 998.

also the output of the variable should get changed based on the selection of values inside item. in that case I am not able to create this variables in script level using peek function.So I am creating two variables in front end only.

attached is the sample report and source file.hope the question is clear.

Re: How to get the last value from a field without having selection on it

swuehl — Tue, 27 Dec 2016 12:17:29 GMT

Why not using

=Max(item,1)

resp.

=Max(item,2)

Re: How to get the last value from a field without having selection on it

sunny_talwar — Tue, 27 Dec 2016 12:22:16 GMT

In your sample, item seems to be from 1 till 1171

But if the goal is find the max value of item and second highest value of item, why not just use Max() function here?

Max(item) -> for max value

Max(item, 2) -> for 2nd highest value