Latest value require in the expression

[Anonymous]

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

[sergio0592]

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]

Hi,

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

[d_prashanthredd]

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]

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]

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;