Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johngouws
Partner - Specialist
Partner - Specialist

Mileage at max datetime

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. 

Data.png

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?    

current2.png

Thank you very much for the assistance. 

 

1 Solution

Accepted Solutions
sunny_talwar

Try this may be

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

View solution in original post

10 Replies
mohan_1105
Partner - Creator III
Partner - Creator III

Hi John,

Could you help us providing the example qvf file?

 

Thanks,

Mohan.

sunny_talwar

Try this may be

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

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

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

johngouws
Partner - Specialist
Partner - Specialist
Author

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. prod_example_1.JPG

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? 

sunny_talwar

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.

johngouws
Partner - Specialist
Partner - Specialist
Author

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

johngouws
Partner - Specialist
Partner - Specialist
Author

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. 

sunny_talwar

How about if you try this

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

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.