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

# Logic of the following calculation fields

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

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
 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])) 2 yesterday { 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

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