Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Latest value require in the expression

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

7 Replies
sergio0592
Specialist III
Specialist III

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

sunny_talwar

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

Anonymous
Not applicable
Author

Hi,

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

d_prashanthredd
Creator III
Creator III

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

sunny_talwar

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?

Anonymous
Not applicable
Author

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.

captain89
Creator
Creator

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;