topic Re: Mileage at max datetime in App Development

Mileage at max datetime

johngouws — Thu, 13 Dec 2018 10:56:41 GMT

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.

Re: Mileage at max datetime

mohan_1105 — Thu, 13 Dec 2018 11:32:52 GMT

Hi John,

Could you help us providing the example qvf file?

Thanks,

Mohan.

Re: Mileage at max datetime

sunny_talwar — Thu, 13 Dec 2018 12:03:05 GMT

Try this may be

Num(Aggr(FirstSortedValue([End OdoMeter], -[Departure DateTime]), [Registration Number]), '# ##0')

Re: Mileage at max datetime

johngouws — Thu, 13 Dec 2018 16:24:45 GMT

Sunny - thank you very much. EXACTLY what I was looking for.

I now need to site and figure what your exp does 🙂

Re: Mileage at max datetime

johngouws — Sun, 06 Jan 2019 17:06:51 GMT

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?

Re: Mileage at max datetime

sunny_talwar — Mon, 07 Jan 2019 00:43:58 GMT

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.

Re: Mileage at max datetime

johngouws — Mon, 07 Jan 2019 01:06:58 GMT

Thanks for this info. I did not know it had to be zipped. This is the example qvf.

Re: Mileage at max datetime

johngouws — Mon, 07 Jan 2019 10:19:21 GMT

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.

Re: Mileage at max datetime

sunny_talwar — Mon, 07 Jan 2019 15:34:48 GMT

How about if you try this

=Num(Aggr(NODISTINCT FirstSortedValue([End OdoMeter], -[Departure DateTime]), [Registration Number]),'# ##0')

Re: Mileage at max datetime

johngouws — Mon, 07 Jan 2019 16:15:29 GMT

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.

Re: Mileage at max datetime

sunny_talwar — Mon, 07 Jan 2019 16:51:38 GMT

Sounds good.