AnsweredAssumed Answered

CASE Date Issue

Question asked by khardy.dsi on Mar 9, 2018
Latest reply on Mar 9, 2018 by khardy.dsi

I have a calculation field that computes the rate for a transportation trip.  These rates change anytime the contract with the vendor changes.  I have used the CASE function in an attempt to track the rate changes but am having an issue with the dates.  The calculation works from January through September but then fails in October.  The DOS Field is a date field.

Case (

TRIPS::DOS < "6/30/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor1" >  total miles*2.06; total miles*2.06;

 

TRIPS::DOS < "6/30/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor2" >  total miles*2.06; total miles*2.06;

 

TRIPS::DOS ≤ "6/30/2017" and PCGV::Vendor Check = "yes"; total miles*2.1;

 

TRIPS::DOS > "10/01/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor1" and TRIPS::total miles  ≤  10 and TRIPS::total miles ≠ 0; 20;

 

TRIPS::DOS > "10/01/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor1" and TRIPS::total miles  >  10; total miles*2.1;

 

TRIPS::DOS > "10/01/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor2"; total miles*2.12;

 

TRIPS::DOS > "10/01/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor1"; total miles*2.06;

 

TRIPS::DOS > "6/30/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor2"; total miles*2.12;

 

TRIPS::DOS > "6/30/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor1" and TRIPS::total miles  ≤  10 and TRIPS::total miles ≠ 0; 20;

 

TRIPS::DOS > "6/30/2017" and PCGV::Vendor Check = "yes" and TRIPS::Mode of Transportation="Vendor1" and TRIPS::total miles  >  10; total miles*2.1;

 

(total miles*Price)+Misc Fees )

 

Example of a trip with an issue is:

 

Trip DOS 1/1/2018 - Vendor 1 - 1 Miles trip is calculating to $2.10.  The trip should be $20 flat fee for less that 10 miles.

Outcomes