3 Replies Latest reply on Aug 22, 2018 8:48 AM by meenu choudhary

    Logic of the following calculation fields

    Haan Haan

      Hi all

      May I know the logic behind/ meaning of the calculation field below.

      Thanks

       

      NoCalculation Field nameCalculation Field
      1

      YTD

      IF DATETRUNC('year',[Trx Date])>={MAX(DATETRUNC('year',[Search Date(TR)]))}

      AND [Trx Date]<={MAX([Search Date(TR)])} THEN [Amount]

      END

       

      NoCalculation Field nameCalculation Field
      1

      branch yesterday

      { FIXED [Branch]:MIN(   IF (DATEDIFF('day',[Trx Date],[yesterday])) > 0 THEN (DATEDIFF('day',[Trx Date],[yesterday])) END

      )} =  (DATEDIFF('day',[Trx Date],[yesterday]))

      2yesterday{ FIXED [Branch]:MAX(IF [Trx Date] = [Search Date] THEN [Trx Date] END)}
        • 1. Re: Logic of the following calculation fields
          Ankit Bansal

          YTD :

          IF DATETRUNC('year',[Trx Date])>={MAX(DATETRUNC('year',[Search Date(TR)]))}

          AND [Trx Date]<={MAX([Search Date(TR)])} THEN [Amount]

          END

          In this first condition is

          DATETRUNC('year',[Trx Date])>={MAX(DATETRUNC('year',[Search Date(TR)]))}

          which means we are considering data for [Trx Date]  YEAR greater than or equal to maximum [Search Date(TR)] year in your data set.

          2nd condition is

          [Trx Date]<={MAX([Search Date(TR)])}

           

          which means [txn date] is less than equal to max [Search Date(TR)]) in your data set.

           

          with both condition are connected by AND, which means only till the latest date of  [Search Date(TR)] for that year , txn date will be considered.

           

          so for example if latest [Search Date(TR)]) in your data set is  21/July/2018, so the amount which are part of txn date from 1/Jan/2018 to 21/July/2018 will be considered.

          1 of 1 people found this helpful
          • 2. Re: Logic of the following calculation fields
            Ankit Bansal
            1

            branch yesterday

            { FIXED [Branch]:MIN(   IF (DATEDIFF('day',[Trx Date],[yesterday])) > 0 THEN (DATEDIFF('day',[Trx Date],[yesterday])) END

            )} =  (DATEDIFF('day',[Trx Date],[yesterday]))

            2yesterday{ FIXED [Branch]:MAX(IF [Trx Date] = [Search Date] THEN [Trx Date] END)}

             

            Here in yesterday, First we are checking for all the records where  [Trx Date] = [Search Date] , And out of those records take the maximum for txn date for each branch.

             

            then in branch yesterday(which is a TRUE|FALSE field) for each branch, if the difference between [Trx Date],[yesterday] is the minimum difference between these 2 fields for each branch (except for the days when both dates are equal) then set it to TRUE else FALSE.

            1 of 1 people found this helpful
            • 3. Re: Logic of the following calculation fields
              meenu choudhary

              HI Haan,

               

              Below is the explanation:

               

              1.  If year of [TRX Date] >= latest year of  "[Search Date(TR)]"  and [Trx Date] <=  latest DATE of "Search Date(TR)" then show "Amount".

               

               

               

              2.  { FIXED [Branch]:MIN(   IF (DATEDIFF('day',[Trx Date],[yesterday])) > 0

                                                           THEN (DATEDIFF('day',[Trx Date],[yesterday])) END )} =  (DATEDIFF('day',[Trx Date],[yesterday]))

               

               

               

              It will return a Boolean value.

               

              For each Branch it is taking the minimum number of days between [Trx Date] and yesterday , where [yesterday] comes after  [Trx Date]  and then compare it with the number of days between  [Trx Date] and yesterday.

               

              3. { FIXED [Branch]:MAX(IF [Trx Date] = [Search Date] THEN [Trx Date] END)}

               

              For each Branch , it is taking the latest [Trx Date] , where [Trx Date] = [Search Date]

              1 of 1 people found this helpful