Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi. I need to calculate when vehicles are nearing their service mileage. I am getting the latest mileage of the vehicle from certain event logs. These events are timestamped. The data is as below: The highlighted line is the correct value I am looking for. I would prefer to have the result in a Dimension rather than a Measure.
When I use "Num(AGGR(max({<[Departure DateTime]={'$(=max([Departure DateTime]))'} >}[End OdoMeter]),[Registration Number]),'# ##0')" it returns the max odometer for all the registration numbers. I need to show the latest odometer reading for each registration number. What am I doing wrong?
Thank you very much for the assistance.
Try this may be
Num(Aggr(FirstSortedValue([End OdoMeter], -[Departure DateTime]), [Registration Number]), '# ##0')
Hi John,
Could you help us providing the example qvf file?
Thanks,
Mohan.
Try this may be
Num(Aggr(FirstSortedValue([End OdoMeter], -[Departure DateTime]), [Registration Number]), '# ##0')
Sunny - thank you very much. EXACTLY what I was looking for.
I now need to site and figure what your exp does 🙂
Hi Sunny.
I wonder if I can pick this up again? I have a very strange scenario. The solution you proposed works 100% in a smaller app. As soon as I use the expression in the new prod app it returns no values for some of the vehicles!! In the test app I include with some sample data it works perfectly. My main test registration is CCCCCCCC. I am using "Num(Aggr(FirstSortedValue([End OdoMeter], -[Departure DateTime]), [Registration Number]),'# ##0')" in the attached sample as well as in the main app. This screenshot is from the prod app and it is exactly the same fields names as per the attached example, but fails in prod. BTW, The example data for CCCCCCCC is the same as per this screenshot.
I attach 3 sample sheets as well as an example qvf. Example_3 is the data attached to CCCCCCCC.
What I don't understand is why when I bring in example data it returns the correct result but when I look at the same registration in the production app I do not get a result. I would appreciate it if you could let me know if there is something else I can do to resolve the issue or what else what you think could cause something like this?
I don't see a qvf attachment... did you zip the qvf before attaching them? This may be needed because qvf cannot be directly uploaded to the community website.
Thanks for this info. I did not know it had to be zipped. This is the example qvf.
Hi Sunny. This expression is giving me the results I am expecting.
Aggr(FirstSortedValue( aggr(max(distinct [End OdoMeter]),[Registration Number],[Departure Date]), Aggr(-[Departure Date],[Registration Number],[Departure Date])), [Registration Number])
My only concern is that it takes longer than previously to calculate the table. I recently upgraded to November 2018 Patch 2 and since then it seems to be slower - but I am not sure if it is my imagination! Can you maybe see a more 'efficient' way of writing this expression?
Thank you for your help.
How about if you try this
=Num(Aggr(NODISTINCT FirstSortedValue([End OdoMeter], -[Departure DateTime]), [Registration Number]),'# ##0')
Thank you for this solution. It returns a similar result to the original solution. I am going to stick to the current solution. I think my problem is actually a data quality issue.
Thanks for all your trouble.