Discussion Board for collaboration related to QlikView App Development.
I require the last/Latest value of Amount on the basis Transaction Date :
Below Function I have used in Set Anlaysis :
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
Try this.. use timestamp() when loading.
"Sales Quote Num",
Timestamp(TransactionDate) as TransactionDate
FROM [lib://Desk/Copy of Set of Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
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?
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.
you can rank sales in script and then take the first value using set analysis:
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
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)
if(Previous([Sales Quote Num])=[Sales Quote Num], peek("subjectRank")+1, 1) as subjectRank
order by [Sales Quote Num], timestamp desc
drop table Sales;