Hi all
except for the standard operators (+,-,*.....) are you aware of a complete list of operators that can be used in "api.Data.Calculate"?
I have checked the official documentation with no success.
In my case I would like to use the Absolute value in a formula that more or less shoud work in this way
api.Data.Calculate("F#ABS_Element = ???AbsoluteValue???( F#ORIGINAL_ELEMENT) )
Thanks in advance for the help
Marcello
What you can use in the formula is documented in the page "Examples of Key Functions in Use" in the Design and Reference guide. Apart from the 4 basic operators, you can use FilterMembers, RemoveNoData, RemoveZero, and Add/Subtract/Divide/Multiply/ConvertUnbalanced - plus Eval. I'm not aware of any other function or operator in the mini-language used by Calculate; there are other ones on the language for GetDataCell, but that works at the individual cell level.
This sounds like this should be done during the data preparation in the Staging Engine before it gets to the Cube. Either in the Import or Validate step or both. By the time the data records are validated and are prepared to load to the Cube, the data records at the base level should already have the absolute values logic applied for their amounts. Once it is loaded to the Cube, it is the raw base level data that is sacred. No manipulation needed and in the case of any audit with this data, there isn't a question why the integrity of the data changes in the Cube.
The raw data in the Cube should be kept sacred and should be the source of truth for your data. The Reporting Engine has the ability to show the data differently such as Henning mentioned above. Using Math.Abs works for Dynamic Calcs for Reporting and for GetDataCell whether in a Cube View or a formula. However, any massaging that needs to be done to the data should be considered during data preparation in Stage.
Hi Tony
Maybe a screenshot is better than explaining in words.
Our goal is to avoid to this
The solution we found in other consolidation system is to have a sys.ERR = abs(err) to put in the validation check.
We were wondering if we could use Math.Abs in the api.calculate but at the moment it seems it cannot be used (unless we use it in a databuffer and set the value cell per cell)
Thanks,
Marcello
Hi Marcello,
We also have a need to calculate absolute values into destination member, Could you please suggest how did you achieve this.
Thanks
Hi Marcello,
not sure what you want to use this for, but I would like to recommend to try not to use api.data.calculate with absolute values. It sounds to me as if you will copy all data to a new member just to get the absolute value. That is likely resulting in unnecessary data duplication.
In OneStream you use math.Abs(yourValue) to get the absolute value. So my recommendation is to create a dynamic member (to avoid unnecessary data duplication) that just returns the absolute value for reporting:
Return math.Abs(yourValue)
Having said that, it all depends on how you wish to use it and what you aim to use it for exactly.
Hi Henning,
I cannot use a dynamic calc because the issue is on the parent element.
The goal is to have the "abosule value" on the leaf and then rollup the "abosule value" on the parent element (which will display the sum of absolute values instead of the parent amount in absolute value)
Are you aware of a list of available operators in the api.Data.Calculate ? Otherwise I think I will have to use a databuffer.
Thanks,
Marcello
There are different approaches possible, e.g. doing this in stage. But to answer your question directly, no, I am not aware of such a function that you can use within an api.data.calculate other than going through the data buffer.
Ok I see what you are trying to do. So you won't be able to overwrite the natural aggregation from base Accounts to a parent Account. The value at the parent Account is dynamically generated from the aggregation of the base members and is not stored. We should be careful changing the base level data or adding additional data records to the data unit to resolve this situation ( can effect overall performance and consolidation times ). I'm guessing those are Flow members in columns and the member "Error" is a member in the Flow Dimension? Then Error is being populated from the Closing Flow member? So is the Flow member "Error" using a stored calc or a dynamic calc? Anyways, you won't be able to use Math.Abs within the api.Data.Calculate. You could also use Math.Abs inside of Confirmation Rules if you choose to make this a Confirmation Rule to support this process as well.
Just wanted to support the need for such a function. This could also be in the form of a special member filter (e.g. A#SomeMember.Base.Absolute)
And to all commentators who warn against changing your "true" values: Remember that OneStream can be used for much more than just accounting purposes. Although the calculation engine at times makes that really difficult to achieve ...
Hi, just for the sake of completeness. Ideas for enhancements may be posted on IdeaStream:
https://community.onestreamsoftware.com/t5/IdeaStream/ct-p/Ideas
That is where the development team checks for community suggestions and demand. The more likes the better and the more attention a given idea will receive. 🙂
There is also a description of the guidelines to ensure an idea posted there gets the right level of attention and helps the development team understand the use case:
https://community.onestreamsoftware.com/t5/Idea-Submission-Guidelines/bg-p/IdeaSubmissionGuidelines
