Need SAP BI report with function to find VIN with tampered odometer mileage

[mikasand]

I want to build a SAP BI document where I process a lots of vehicle data in order to find vehicles with tampered mileage odometer. The data table include VIN, and Date / Mileage at each service visit.

I am absolutely a beginner and need help with some simple coding, how to set up the variables in SAP BI 4.2 Web interface, so the report can process and find the VINs with manipulated mileage odometer.

Normally the mileage increase when the car is used. I want to find these VIN's where mileage has decreased with previous date.

The example show 2 VIN's which one have a tampered mileage.

sap-bi-example-picture.jpg
sap-bi-example-text.txt

[amitrathi239 · Accepted solution]

use below formula. Filter on where greater than 0

V Mileage=Sum(If([Milage]<Previous([Milage];([VIN]))) Then Previous([Milage];([VIN]))-[Milage] Else 0 )ForEach([VIN])

To display the difference in mileage use below formula.

=nofilter([V Mileage])

[amitrathi239]

Follow below steps.

Sort the table data in ascending order by VIN and date.

Add extra column and place bthe below formula

V Milage=If([Milage]<Previous([Milage];([VIN]))) Then Previous([Milage];([VIN]))-[Milage]

Filter on the V Milage variable whwre greater than 0

[mikasand]

Thanks! It worked great after adding the Nofilter . Report now working as I want to !

[mikasand]

Thanks!

It was working to find all the tampered VIN's, however the value in the added column "V Mileage" is blank after applying the filter "Greater than" = 0. The report is working since I can find the concerned VIN's, BUT it would be nice to know how much mileage has been tampered. If it is easy to fix to display also the Lowered Mileage, please assist.