topic Latest value require in the expression in QlikView

Latest value require in the expression

Anonymous — Wed, 20 Jun 2018 18:32:29 GMT

Hi,

I require the last/Latest value of Amount on the basis Transaction Date :

Below Function I have used in Set Anlaysis :

FirstSortedValue(Amount,-TransactionDate)

But above when used in my code its not working.fine

Please find the attached set of data.

Kindly let me know how we achieve one row with the current amount value as 250.17

Regards

Re: Latest value require in the expression

sergio0592 — Wed, 20 Jun 2018 18:49:20 GMT

Hi, in your sample transaction date as the same value for all Sales Quote num.

Re: Latest value require in the expression

sunny_talwar — Thu, 21 Jun 2018 01:59:45 GMT

Where exactly are you using this expression? Text box object or chart? What is the dimension if this is used in a chart?

Re: Latest value require in the expression

Anonymous — Thu, 21 Jun 2018 09:05:35 GMT

Hi,

I am using this expression in Straight Table and dimension I am using as Amount and Transaction Date.

Re: Latest value require in the expression

d_prashanthredd — Thu, 21 Jun 2018 09:54:59 GMT

Hi Manisha,

Try this.. use timestamp() when loading.

LOAD

"Sales Quote Num",

Amount,

Timestamp(TransactionDate) as TransactionDate

FROM [lib://Desk/Copy of Set of Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Thanks,

Prashanth

Re: Latest value require in the expression

sunny_talwar — Thu, 21 Jun 2018 11:11:07 GMT

Why do you use Amount and Transaction Date as dimension, if you want the expression to be aggregated to show max(Transaction Date) and Amount associated with Max(transaction date)... Can you remove the dimension to see if this works?

Re: Latest value require in the expression

Anonymous — Thu, 21 Jun 2018 13:23:22 GMT

Hi Prashanth,

With this setanalysis : sum(aggr(FirstSortedValue(Amount,-TransactionDate),[SalesQuote Num])) I could not able to get one row as you have got it.

Please find the attached output sheet.

Re: Latest value require in the expression

captain89 — Thu, 21 Jun 2018 16:35:53 GMT

Hi,

you can rank sales in script and then take the first value using set analysis:

sum({$<subjectRank={"1"}>} Amount)

Sales:

LOAD [Sales Quote Num] as [Sales Quote Num],

Amount as Amount,

Time(Frac(TransactionDate)) as time,

Date(Floor(TransactionDate), 'YYYY-MM-DD') as date,

timestamp#(TransactionDate, 'DD/MM/YYYY hh:mm:ss') as timestamp

FROM [Set of Data.xlsx] (ooxml, embedded labels, table is Sheet1)

;

//create the rownumber

RankSales:

load *,

rowno() as Subrank resident Sales

Order by [Sales Quote Num], date desc, time desc

;

//create rank based on quote num, date and time

left join (RankSales)

load Subrank,

if(Previous([Sales Quote Num])=[Sales Quote Num], peek("subjectRank")+1, 1) as subjectRank

Resident RankSales

order by [Sales Quote Num], timestamp desc

;

drop table Sales;