1 Reply Latest reply on Mar 28, 2017 3:24 PM by Shinichiro Murakami

    Finding Date Difference for Pivot fields -Tab. 10

    Richa Tripathi

      Hello,

      I need help finding the date difference between all the 'Pivot Field names' with respect to the 'Target date'.And plot them over with arrows upward and downwards.

      I want to show the current marks on the graph and the upward -downward arrows indicating whether they met the target date or not.

       

      As I understand there are two parts to this problem.

      1) finding the difference between the 'target date' and the 'Pivot Field names' eg. (Enter in FPRS -minus- Target date)

      2) Once I have the above difference, will have to compare if its greater than and equal to 3 or not. If >= 3 "upward arrow"-met the target else "downward arrow"

       

      Attached is the file.

      Please share your input.

       

      Thanks,

      Ric

        • 1. Re: Finding Date Difference for Pivot fields -Tab. 10
          Shinichiro Murakami

          Hi Richa

           

          I hope I understand your request.

           

           

          First, I only find "REPORT COMPLETE  (w/in 8 BDs prior to due date)" as unique project ID, so I used this field to calculate target for each.

           

          [Target Date]

          if [Pivot Field Names]="Target Date" then [Pivot Field Values] end

           

           

          [Target Date Fixed]

          {fixed [REPORT COMPLETE  (w/in 8 BDs prior to due date)]:min([Target Date])}

           

          [Date Diff]

          [Pivot Field Values]-[Target Date Fixed]

           

          [Target days]  // This field is based on my guess.

          case [Pivot Field Names]

          when "ENTER IN FPRS (3 w/in BDs prior to due date)" then 3

          when "PROGRAM SERVICES (PS) MANAGER REVIEW (w/in 4 BDs prior to due date" then 4

          when "REVIEW COMPLETE  BY  FINANCIAL ANALYST(S) (w/in 5 BDs prior to due date)" then 5

          when "Submit to Grants for Certification (1 w/in BDs prior to due date)" then 1

          when "SUBMIT TO WIC DIRECTOR  FOR  APPROVAL (4 w/in BDs prior to due date)" then 4

          end

           

           

          [Arrow]

          If [Date Diff]>= {fixed [Pivot Field Names] :min([Target days])}  then "up" else "down" end

           

          Thanks,

          Shin

          1 of 1 people found this helpful