Rollup Options - Cube Viewer

Arc & Slice
1

Hi Arc team,

A few questions related to the “Rollup” feature in the Arc cube viewer. Thank you in advance!

  • Wanted to check where we could find info on the differences between “AGGREGATE / SUM” and “VAR / SUBTRACT”. These pairs seems to function similarly in simple use cases when selecting two elements.

image
image976×926 67.3 KB

  • After creating one rollup, Arc would give the error message below when trying to create a different one. Is the number of rollups on one dimension limited to 1 at a time? If so, what’s the suggested way to reset things to update the type of rollup? The best way I’ve found is to undo the Cube view to an earlier one where the first rollup was not yet defined and start creating the second rollup from there.

image

1 Like
2

Hi Wei,

These are MDX functions, to learn more about these functions, you can find them in the list of MDX functions here: Aggregate (MDX) - SQL Server | Microsoft Learn

Yes Aggregate and SUM are doing the same things with numeric values.

Could you please create a ticket for this one and add the screenshots and the detailed steps to reproduce it?

3

Aggregate and Sum are similar functions but there are differences how mdx engine produces results.

VAR and SUBTRACT are completely different functions.

AGGREGATE
Function returns a number that is calculated by aggregating over the cells returned by the set expression. If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure. If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.

SUM
Function returns the sum of a numeric expression evaluated over a specified set.

VAR
Function returns the sample variance of a numeric expression evaluated over a set, using the unbiased population formula (dividing by n).

SUBTRACT
This one is a custom function. If you select three elements: e1, e2, e3. First element stays positive and the rest becomes negative like so rollup=e1-e2-e3

4

Thanks for clarifying guys! I was thinking that they were all core MDX functions but then noticed “subtract” so wanted to check if the other ones were also customized in some way.

The main reason I had asked was because we have been starting to expose the cube viewer to more non-TM1 users and wanted to see if there was a way to simplify the custom rollup options. The most common actions these users take are creating sums and variances. There are multiple ways to do this so we are trying to find some ways to simplify.

Maybe its a matter of having a way to define globally which of these custom rollup options are exposed to users. I’ll put in an enhancement proposal for this. Thanks again!