This functions provides native support for doing balances, e.g. for onhand inventory or general ledger
balances. CloseBal returns the closing balance – which is the sum of the measure since beginning of time
including “todays” transactions.
Syntax:
SUM(CLOSEBAL(measure,[DateColumnName]))
Note: In case there are no transactions for a specific date the date will be excluded from the output. See
also: OPENBAL.
Example:
SUM(CLOSEBAL(measure,[DateColumnName]))
Comments
Is it possible to round the result? For example if the result is 0.000000000000000000012, I would like to change the value into 0.00. So not only the format but the value.
The reason I need it, is to use the visibility agent.
Hi Jora,
The InMemory ETL script offers a ROUND function: https://community.targit.com/hc/en-us/articles/360017716977-ROUND-number-decimal-places-
You can also round values in the end-user client: https://community.targit.com/hc/en-us/articles/360016972237-Result-modifiers
E.g., in the client you could create a calculation like round(sum(d1, 0, m1);2) - this calculation can also be used directly in a visibility agent.
BR / Ole
Ole, I have the need to have the sum of my closebal measure for a period of time. For instance, if I have a YTD criteria, I'd like to have the sum of the closebal for each month. Is that possible without putting the month in the object?
Hi Danilo.
Let me try to help you. Can you provide me of your concrete example for me to understand what you are trying to achieve?
Is it like a cross table covering a YTD period with no month dimension but with a sum of a CloseBal() measure?
Hi Allan, that is exactly it.
I need the sum of the CloseBal measure across the Year.
Hi again.
I'm afraid that I need that very concrete example to be able to help you.
I'm certain that it can be solved but I'm not sure whether it should be solved in the backend or the frontend.
Best Regards,
Allan
Please sign in to leave a comment.