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

# Percentage calculations based on the the certain criteria

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

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. • ###### 2. Re: Percentage calculations based on the the certain criteria

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

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

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