5 Replies Latest reply on Apr 22, 2019 5:29 AM by DHRITIMAN BANERJEE

# LOD and Running Sum Scenario

Hi Community, As per the screenshot , Monthly Original data shows the original data monthlywise.

1)BOP Total value should have BOP TOTAL value of July 2018 which is minimum month of the year for all the corresponding months as per the excel image by using LOD .

2)Running SUM should be applied for COI Charge amount and Investment Performance only.

3) Running Sum should not be applied for BOP Total Account Value ,  EOP Total Account Value and BOP Total account value should have July month value for all the months.

Referring to Monthly Running Total, you can observe running total have been applied to BOP and EOP Total account value when I use table calculations: running total which is wrong as per business. Business wants running total for COI Charge amount and Investment performance only which is the main challenge.

Jim Dehner  Shinichiro Murakami Could you please go through the scenario as you helped me previously to solve such scenarios

I have attached my workbook for reference and currently using 10.2 version.

Regards,

Dhritiman

Note: Logic tried by me for reference but it is wrong

1)beg end dates : Logic which I want to find July month      BOP Total Account value and implement july month value for all the other months related to BOP Total account value

{ FIXED DATEPART('month', [Vltn Date]),[Pivot Field Names]:

if min([Pivot Field Names])="BOP Total Account Value" then Min(DATETRUNC('month', [Vltn Date]))

end   }

2)beg end values

if DATETRUNC('month', [Vltn Date])=datetrunc('month',[beg end dates]) then [Pivot Field Values]

elseif [Pivot Field Names]="COI Charge Amount" then  [Pivot Field Values]

elseif [Pivot Field Names]="Investment Performance" then  [Pivot Field Values]

elseif [Pivot Field Names]="EOP Total Account Value" then  [Pivot Field Values]end

• ###### 1. Re: LOD and Running Sum Scenario

Hi Dhritiman,

Find the attached workbook you will get the required O/P:

Hope this helps

BR,

NB

1 of 1 people found this helpful
• ###### 2. Re: LOD and Running Sum Scenario

Thanks , Naveen for the solution

• ###### 3. Re: LOD and Running Sum Scenario

Hi Naveen, Please refer the Business Requirements as per the Image, I do not want to fix the EOP Total Account value as you have implemented for BOP Total Account value. EOP Total Account value will show its corresponding month data like the excel, only BOP Total Account Value will be fixed and show July Month data for all the corresponding months which you have implemented correctly.

Could you please modify the logic for EOP Total account value which will show the corresponding months data only.

Thanks and Regards,

Dhritiman

• ###### 5. Re: LOD and Running Sum Scenario

HI Naveen ,

Thanks for the solution.

I need one help from you regarding quarterly scenario. Could you please go through the scenario as I have urgent delivery. As per the quarterly scenario,

1)EOP Total Account value should show value for the maximum month of the quarter.

Example : For Q3, EOP Total Account Value should show value for September and For Q4, EOP Total Account Value should show value for December if you refer  the 3rd excel image

2)The calculations for other columns are okay . So, does not need change

Could you please help me to implement  the LOD logic for quarter calcuations of EOP Total Account Value.

Regards,

Dhritiman

Note: calculations used by me for quarterly calculations of EOP Total Account value  which runs fine previously  when running_sum is not  introduced.

1)Created a calculated date : quarter end dates

{ FIXED DATEPART('month', [Vltn Date]),[Pivot Field Names]:

if min([Pivot Field Names])="EOP Total Account Value" then max(DATETRUNC('month', [Vltn Date]))

end   }

2)Create a metric for EOP Total account value

if ([Pivot Field Names])="EOP Total Account Value" and  DATETRUNC('month', [Vltn Date])=datetrunc('month',[quarter end dates]) then [Pivot Field Values] end