Cumulative context but to exclude specific status measures

Nicholas_Campbell
4 yrs ago
3replies
Answered

Good evening

I have a report I'm trying to replicate and I've managed to get most of it working but I need to either eliminate two cells as part of a measure or or recalculate the cumulative total to exclude 07 . Status (this is an aggregate of several elements in a dimension and not a specific dimension)

Using the above as an example I need to remove the measure Σ - GWP for status 07. Cfwd so that the cumulative total skips and goes to 08. Renew and then also excludes 09.Total? (both highlighted)

I'm assuming as cumulative total is context it won't work, will the new version be able to achieve this?

Thanks

nick

- Nicholas_Campbell
- 4 yrs ago
- Reported - view
Hi

In your last training course, you said cumulative totals (semantic) are possible in the new version, is it possible to get some example instructions on how that would work when available?

Many Thanks

Nick
- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
Hi Nick,

Yes, you can.

This relies on the upgraded CurrentMember() PQL function which in 2020.10 returns the Member Name where required instead of just the value.

Create a new calculated Measure, Skip. This examines each member of Status in the grid and if it equals 07. Cfwd or 09. Total, sets the value to 0, otherwise to the existing GWP value:

Be sure to make sure it resides in the Measure dimension.

Then use the Context Create Cumulative on Skip to build the running total:

You can of course, then eliminate the Skip column from the grid.

Hope that helps.

Ian
- Senior Director of Product
- Ian_Macdonald
- 4 yrs ago
- Reported - view
Hi again,

You asked for some examples of creating cumulative totals. This, again, makes use of the upgraded CurrentMember function, usually in combination with the Range function.

You mention that your rows are themselves aggregates of other members. Unfortunately, the Range function relies on the order of members as defined in your Model, so doesn't work with custom members as they have no natural order.

However, if your row members of attribute Status were model members, then this is how you would approach the cumulative total problem.

It operates on each row in turn, creating a set from the RANGE(starting member, current member) and aggregating them.

Adding this measure to our grid:

This looks identical to the Context Cumulative, ∑ Skip, but watch what happens when we eliminate some of the rows:

The Context Cumulative, ∑ Skip, operates only on the data present in the grid, hence the name for these types of calcs, Context Calculations.

The manually created Cumulative Skip operates against the Semantic model and will always include all the items in the defined range, regardless of what is displayed in the grid.

Hope that helps, both for your immediate problem and also for better understanding cumulative calculations and CurrentMember().

Ian