Calculated Dimension that Uses Complete Set

[peter_burgess]

Hi.

I have a situation where I am trying to restrict the Dimensions in a straight table, and preferably by not restricting them via the expressions since these are quite complicated and involve a number of other dimensions.

To better explain I will try and simplify my question this way:

I have 5 company Plants: A, B, C, D and E.

Each Plant has Stock on hand and average weekly Sales, so the expressions (remember, very simplified) are sum(Stock), sum(Weekly Sales), and Weeks' Cover=sum(Stock)/sum(Weekly Sales).

There is one straight table that has a great deal of detail in it and applies to all 5 Plants.

But I want to create a specific Summary Table that applies to ONLY Plants A, B and C, not showing D and E at all.

Plant	Stock	Weekly Sales	Weeks' Cover
A	15	3	5.0
B	20	8	2.5
C	30	10	3.0

I want to make the Plant Dimension show all 3 Plants, regardless of whether a Plant might be excluded due to other dimensions' selection, i.e. if the Plant isn't relevant to the selection then the sum(Stock) should show 0, and so should the Weekly Sales.

My problem is, I can't seem to get all 3 Plants to remain showing.

A Calculated Dimension that half works is, If(Plant='A' or Plant='B' or Plant = 'C', Plant, null()) - meaning that Plants D and E will not show (as long as null dimensions are suppressed). However, as soon as a selection occurs that excludes one of the wanted Plants (let's say this is because a particular Product is selected and it isn't stocked in Plant C) , then the row with C in it disappears. I want Plant C to show, sum(Stock) to = 0, and sum(Weekly Sales) also 0 (Weeks' Cover would show -). Really, I just want the row headed by C to remain.

I think I need to have an aggr() statement in the Calculated Dimension, and somehow refer to the Complete Set {1}, modified to exclude Plants D and E.

But I can't seem to achieve that and I don't know whether it's because my syntax is wrong - highly likely! - or because it's just not possible.

Can anyone help me here?

Thanks and regards,

Pete

[jonathandienst]

Using set expression in the calc dimension to prevent selection of plant affecting summary:

Aggr(Only({<Plant = {'A', 'B', 'C'}>} Plant), Plant)

[jonathandienst]

Or exclude specific plants

Aggr(Only({<Plant -= {'D', 'E'}>} Plant), Plant)

[peter_burgess]

Thanks Jonathan.

I want to aggregate over the complete set, so modified your formula to read:

Aggr(Only({1 <Plant = {'A', 'B', 'C'}>} Plant), Plant)

But - this still doesn't aggregate over the whole set, somehow I need to poll all the Plants that are available. I need something like:

Aggr(Only({1 <Plant = {'A', 'B', 'C'}>} Plant), {1} Plant)   or

Aggr({1} Only({1 <Plant = {'A', 'B', 'C'}>} Plant), Plant)

but of course neither of these has the correct syntax.

Pete

[Digvijay_Singh · Accepted solution]

You may try something like this -

What does it mean sum({1} 0)?

[swuehl]

Remember that you disable 'suppress zero values' on presentation tab (I guess your expressions return zero for dimension values that are excluded by user selection, which leads to removal of that lines by that presentation option).

edit: also, adding an expression with a constant 1 and hiding the expression might help

[peter_burgess]

Thanks Digvijay, this gave me the result I wanted.

By adding +Sum({1} 0) to the end of both my expressions for Stock and Weekly Sales, and going back to my original calculated dimension formula of  If(Plant='A' or Plant='B' or Plant = 'C', Plant, null()), then the wanted Plants always display, even if they have 0.00 as a value.

There is always a way!!

Pete

[Digvijay_Singh]

Credit goes to our legends swuehl / stalwar1, as I remember they suggest these tricks quite often here.