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

    LOD and Running Sum Scenario

    DHRITIMAN BANERJEE

      Hi Community,

       

      Please refer the screenshot for your reference.

      Tableau.JPG

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

      But as per the business,

      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.

       

      Could you please help me to implement the logic as per the business requirements.

      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
          Naveen B

          Hi Dhritiman,

           

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

           

          Hope this helps

          Plz mark this answer as correct & helpful to close the thread

           

          BR,

          NB

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

            Thanks , Naveen for the solution

            • 3. Re: LOD and Running Sum Scenario
              DHRITIMAN BANERJEE

              Hi Naveen,

               

              Could you please help me to modify the logic for EOP total account value

              Tableau2.JPG

               

              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

              • 4. Re: LOD and Running Sum Scenario
                Naveen B

                Check the attached workbook

                 

                change the cal to below

                 

                Hope this helps

                BR,

                NB

                • 5. Re: LOD and Running Sum Scenario
                  DHRITIMAN BANERJEE

                  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.

                  quarterly issue.JPG

                   

                  Please refer Monthly ,Quarterly wrong data  and Business Requirements screenshot.

                  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