How to perform YTD aggregation on data located in parent child hierarchies with unary operators?
I am using parent-child relationship for accounts in iccube OLAP database. To enable financial logic, I use unary operators. Also, I have set up a few accounts using a many-two relationship and everything works very smoothly except ....
when I want to apply temporary logic to the result, eg. show YTD value for April 30, 2014:
Aggregate(crossjoin ({[View].[View].[Periodiek]},PeriodsToDate([Tijd].[Kalender].[jaar],[Tijd].[Kalender].currentmember)))
I get the message:
Aggregate (): unary operator aggregator not supported (dimension or calculated dimension / member: [Measures]. [Bedrag])
Apparently this is not the way to do it.
How can you achieve cumulative key figures (periods to date) in this setting?
source to share
The current version of icCube - 4.8.2 - does not support the Aggregate function for measures with a unary aggregation type operator. See the aggregation function doc here .
The Aggregate function is a bit dodgy if you are using many-two-many relationships as well as special measure aggregation types. For example:
Aggregate( { [Account1], [Account2] }, [Measures].[Special] )
If [Special] is a measure with aggregation type "Amount", and [Account1] and [Account2] have a "many-two" relationship, we will count the same "total" amount twice (aka the row is counted twice).
Other measures with aggregation types are simply not supported to avoid unexpected results. This applies to aggregate types: Open / Close / Distinguish Account.
The solution in your case is using the Sum function:
- Sum ( CompactSet ( PeriodsToDate ([Tijd]. [Kalender]. [Jaar], [Tijd]. [Kalender] .currentmember)), [View]. [View]. [Periodiek])
The compact set allows you to set up compactly if you are using days or hours by reducing the set. It's a productivity booster.
If you want to properly handle m2m relationships and special measure aggregation types, you can use Categories in icCube, see doc here . Fast, they allow you to dynamically define elements as a set of tuples.
To properly support Aggregate, we have to add a new method, eg. icAggregate, which works with categories. The aggregate function is a little weird, while we mimic SSAS a bit ...
source to share