FirstSortedValue()

[johngouws]

I have a situation where I have Vehicle related data in a table. In the table, among other fields, I have the Registration, Transaction Date and Amount. Once per month there is a Vehicle Lease Transaction around the 25th of the month. Although we are 2019-07-16, the last transaction was on 2019-06-25. Over time the lease amount can increase or decrease. I need to show only the latest Lease Amount. 

Among many tried expressions, this one is the closest, but still not correct. I have tried removing the [-] before the [Transaction Date] but this does not appear to make a difference. 

Aggr(firstsortedvalue({<DateType={'Daily_Data'},tx_Summary={'LEASE'}>} Amount, -[Transaction Date]), [Registration Number])

Please can you show me where am I going wrong with this? Thanks. 

[mato32188]

Hi John,

could you please try following:

if(TranDate=aggr(nodistinct max(TranDate),RegNo),1)*aggr(sum(Amount),RegNo,TranDate)

with your set modifiers.

Thanks.

BR

Martin

[johngouws]

Hi Martin, 

That is a very helpful option, thanks. It is unfortunately assuming the [Transaction Date] is in the object. In the example a gave i did include the [Transaction Date] as an example. In practice the [Transaction Date] is not in the object. All values are Aggr to Registration level. Thanks any way for your solution. 

John

[tresesco · Accepted solution]

If you are using the registration field in the table, you could remove your aggr() from the expression and alone the exp:

firstsortedvalue({<DateType={'Daily_Data'},tx_Summary={'LEASE'}>} Amount, -[Transaction Date])  should work. If that is not working, try to share a sample app to check.

[johngouws]

Your solution is exactly what I was looking for. The Registration Number is in the table. So, when I use the solution as an expression "firstsortedvalue({<DateType={'Daily_Data'},tx_Summary={'LEASE'}>} Amount, -[Transaction Date])" works perfectly. I then need to set the calculation method to 'sum' instead of 'auto'. 

When I use the solution as a Dimension I use "=Aggr(firstsortedvalue({<DateType={'Daily_Data'},tx_Summary={'LEASE'}>} Amount, -[Transaction Date]),[Registration Number])" and it gives the correct values, obviously without a total. 

Thank you very much - it is a big help.