topic Re: Top last in QlikView

Top last

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

I have a table which has release name and amount

Like below

ReleaseName Amount

S12.1 27

S12.2 40

S12.3 20

S12.4 22

and so on

in a text box i need to show the last release amount

how can i achive that .....

for example considering above table in a text object i need to show

22 which is for last release S12.4

Thans in Advance

Re: Top last

tresB — Wed, 29 Aug 2018 07:32:22 GMT

Try:

=FirstSortedValue(Amount, -KeepChar(ReleaseName,'0123456789'))

Re: Top last

smilingjohn — Wed, 29 Aug 2018 07:40:54 GMT

Hi Tresesco,

Thanks for the reply , But this is not working . this shows a dash in text object .

Re: Top last

prashantsanchet — Wed, 29 Aug 2018 07:57:30 GMT

Please try to use below expression

FirstSortedValue(distinct Amount, -KeepChar(ReleaseName,'0123456789'))

This expression gives expected output.Please find attached screenshot

Regards,

Prashant

Re: Top last

tresB — Wed, 29 Aug 2018 07:59:13 GMT

It's working with me.

Re: Top last

smilingjohn — Wed, 29 Aug 2018 08:05:07 GMT

Thanks For the reply prashanth ..

For real scenario i have the data as shown in the screenshot ..

For this when the release R-18.7 is the latest one (last value) and when summed the hours should be 491 for R-18.7

with your suggested expression i am getting only 70

Thanks

Re: Top last

prashantsanchet — Wed, 29 Aug 2018 08:15:53 GMT

Hi,

In this case use below expression:

FirstSortedValue(distinct aggr(sum(Amount),ReleaseName), -KeepChar(ReleaseName,'0123456789'))

Regards,

Prashant

Re: Top last

prashantsanchet — Wed, 29 Aug 2018 09:15:41 GMT

Hi ,

If this is correct solution ,please mark as correct answer.

Regards,

Prashant