Hi Team,
We are currently working on calculating the operational cash flow KPI in NSAW, using the following formula:
Operational Cash Flow = Net Income + Changes in Working Capital
The breakdown for each component is as follows:
- Net Income is calculated as:
Net Income = Income Account Type - COGS Account Type - Expenses Account Type - Working accurately - Changes in Working Capital is determined by:
Changes in Working Capital = (Current Assets + Current Liabilities){2024} - (Current Assets + Current Liabilities){2023}
For a more detailed view, the calculation for changes in working capital includes:
Year 2024 ((AR + Bank + Other Current Assets) + (AP + Credit Card + Other Current Liabilities)) - Year 2023 ((AR + Bank + Other Current Assets) + (AP + Credit Card + Other Current Liabilities))
However, I'm facing an issue with the formula for all the account types to get balances - for example to get the Accounts Receivable (AR) balance for 2024. Here’s the formula I’m using:
(CASE
WHEN "NetSuite - Account Analysis"."Dim - Date NetSuite Gregorian Calendar"."Year" = 2024 THEN
ABS((CASE
WHEN "NetSuite - Account Analysis"."Dim - NetSuite Account"."Account Type" IN ('Accounts Receivable') THEN
"NetSuite - Account Analysis"."Custom_DW_NS_AA_APP_ACCOUNT_ANALYSIS_F"."ACCOUNTINGBOOK_NETAMOUNT"
ELSE 0
END))ELSE 0
END)
The problem is that, when added this calculation in visualization despite filtering for the AR account type and the year within the formula, I still need to apply manual filters to obtain accurate results. Meaning we should apply filters manually for the account type (Accounts Receivable) and the year (2024) in visualization then the values come out correctly.
Could anyone assist me in resolving this issue? I would like to ensure that the formula itself yields accurate values without the need for additional manual filtering.
For your reference, I am utilizing the 'NetSuite Account Analysis' Subject Area and the AccountingBook Net Amount column for this requirement.