
1. Re: Percentage calculations based on the the certain criteria
swaroop.gantela Oct 9, 2018 6:41 PM (in response to Tech Guru)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.25ELSEIF
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.twbx 14.4 KB


2. Re: Percentage calculations based on the the certain criteria
Tech Guru Oct 9, 2018 11:42 PM (in response to swaroop.gantela)1 of 1 people found this helpfulThanks 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

3. Re: Percentage calculations based on the the certain criteria
swaroop.gantela Oct 10, 2018 8:49 AM (in response to Tech Guru)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 Oct 11, 2018 4:56 PM (in response to swaroop.gantela)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