topic Re: Last() function in QlikView

Last() function

Tue, 28 Jul 2015 19:02:30 GMT

I am trying to pull values into a straight table using the Last() function.

This is what my table looks like now, however the dates are incorrect. I need to pull in the last promise date and last delivery dates for each of the values on the left. All of the numbers on the left have different lives, so some go from, for example, March to April, or possibly May14-20, or May through June 15. The timeline of the left numbers vary greatly.

The data is loaded into Qlikview using many different Excel files and many load statements, otherwise I would use LastValue(), although that might end up being the easiest way to do this.

Re: Last() function

marcus_sommer — Tue, 28 Jul 2015 19:09:51 GMT

You need for this firstsortedvalue([Latest Promise Date], -[Latest Promise Date]).

- Marcus

Re: Last() function

Tue, 28 Jul 2015 20:37:57 GMT

I tried that but it only worked for the sales doc #s that only had one promise date and one delivery date inputted, the rest came up null.

Re: Last() function

marcus_sommer — Wed, 29 Jul 2015 07:02:40 GMT

Yes, firstsortedvalue() had the limitation that it only worked if the rank-place is unique - sometimes you could adjust this with a set analysis within the expression but I think you should max() give a try:

date(max([Latest Promise Date]), 'MM/DD/YYYY')

And last() is a inter-record function and isn't suitable for this then it's only for pivot-tables available and inter-record function calculates in context to other rows and columns within the pivot and not against the dataset itself.

- Marcus

Re: Last() function

sunilkumarqv — Wed, 29 Jul 2015 07:06:36 GMT

Use peek Function to get last field

In script .

Let vLastvalue = Peek('FieldName',-1,'TableName');

date(maxstring([Latest Promise Date]), 'MM/DD/YYYY')

Re: Last() function

Wed, 29 Jul 2015 16:56:40 GMT

I tried max() and it gives me the highest value, not the last value. I just tried Aggr(FirstSortedValue(,-[Date]),[Sales Doc. & Line No.] which works better.