4 Replies Latest reply on Oct 11, 2018 4:56 PM by Tech Guru

    Percentage calculations based on the the certain criteria

    Tech Guru

      Need some suggestions on a specific calculation

       

      I have a formula as mentioned. This works perfectly fine but I need some additional condition to be added to this. So, basically, I want these two conditions to be added in one formula as CapEx /Opex would be the data split and I can't have two calculations separately.

      I need some assistance as this is a little tricky.

       

      Condition 1) Below condition is when WBS is null.......

      IF (ISNULL(ATTR([InvoiceDetailInfoSecure].[WBS])))

      AND ATTR([Filing Date]) <= #07/01/2015# AND ATTR([Current Status]) = 'Issued'

      OR  ATTR([Filing Date]) >= #07/01/2015# AND ATTR([Current Status]) = 'Abandoned'

      THEN 'Opex'

      ELSE 'Capex'

      END

       

      Condition 2) If cost center is null then split the Line item amount into 75% + 25% as Capex/Opex.. Capex should be 75 % of 'Line Item amount' and opex should be 25% of Line item amount.

       

      If (ISNULL(ATTR(ATTR([InvoiceDetailInfoSecure].[Cost Center])))

      THEN 'Capex' = (([Line Item Amount] * 75) / 100) and Opex =  'Opex' = (([Line Item Amount] * 25) / 100)  --- or something like that..not sure how to calculate this.

       

      Sample data: Final output is from both the conditions

                                                                     

      Inv         cost center          wbs       filing date            current status            line item amount       capex               opex

      1            123                     null        01/01/2015             issued                              100                                            100

      2            456                     null        01/01/2016             abandoned                      200                                             200

      3                                       789                                                                              400                    300                   100

        • 1. Re: Percentage calculations based on the the certain criteria
          swaroop.gantela

          Apologies, I didn't quite catch the gist of the requirement not to use two separate calculations.

           

          I took a stab at it, but it required calculating capex and opex separately:

           

          Calculation for [Capex]:

          IF ISNULL(ATTR([Cost Center]))

          THEN SUM([Line Item Amount])*0.75

          END

          //wasn't sure if there were other conditions that filled capex

           

          Calculation for [Opex]:

          IF ISNULL(ATTR([Cost Center]))
          THEN SUM([Line Item Amount])*0.25

          ELSEIF
              ISNULL(ATTR([Wbs]))
              AND
                  (ATTR([Filling Date]) <= #07/01/2015# AND ATTR([Current Status]) = 'issued'
                  OR
                  ATTR([Filling Date]) >= #07/01/2015# AND ATTR([Current Status]) = 'abandoned')

          THEN SUM([Line Item Amount])

          END

           

          Please see workbook v10.3 attached in the Forum Thread.

           

          284327split.png

          • 2. Re: Percentage calculations based on the the certain criteria
            Tech Guru

            Thanks Swaroop.

             

            Your solution is very close to what I am looking for.

             

            Only the problem I am seeing with second part where WBS is not calculating properly. When there is a cost center and WBS is null, the calculation is returning null for Capex and Opex both. Do you see any problem with  ELSEIF condition?

             

            Here is the output screenshot and formula.

             

            IF ISNULL(ATTR([TMo_InvoiceDetailInfoSecure].[Cost Center]))

            THEN SUM([Line Item Amount])*0.25

             

            ELSEIF

                ISNULL(ATTR([TMo_InvoiceDetailInfoSecure].[WBS]))

                AND

                    (ATTR([Filing Date]) <= #07/01/2015# AND ATTR([Current Status]) = 'Issued'

                    OR

                    ATTR([Filing Date]) >= #07/01/2015# AND ATTR([Current Status]) = 'Abandoned')

             

            THEN SUM([Line Item Amount])

             

            END

             

             

             

            1 of 1 people found this helpful
            • 3. Re: Percentage calculations based on the the certain criteria
              swaroop.gantela

              Hmm. Looks like it should work.

              Could you post a screenshot with the Filling Date shown?

              Specifically, if you could add as a column ATTR([Filling Date]).

              • 4. Re: Percentage calculations based on the the certain criteria
                Tech Guru

                I just changed AND to OR and it worked. Thanks for your help.

                 

                ELSEIF

                    ISNULL(ATTR([TMo_InvoiceDetailInfoSecure].[WBS]))

                    OR

                        (ATTR([Filing Date]) <= #07/01/2015# AND ATTR([Current Status]) = 'Issued'

                        OR

                        ATTR([Filing Date]) >= #07/01/2015# AND ATTR([Current Status]) = 'Abandoned')

                 

                THEN SUM([Line Item Amount])

                 

                END