calculated dimension

[Anonymous]

Hi All,

I have a scenario where a dimension has 4 fields (Columns), for example lets say Dimension is X and 4 fields(Columns) in  it be A, B, C, D which have some numerical values.

When I create a pivot table with the dimension  X and  aggregation function (ex: SUM)  in the expression, the output should only show the values of A, B fields(Columns) excluding C and D fields(columns) in the dimension X.

How can this be achieved?

Thanks In advance,

Pavan

[el_aprendiz111]

Hi,

[Anonymous]

If A,B,C,D are Primary keys, you feel no difference.

Usually, Pivot is considered for Grouping and Subtotals like below

If A distinct values, B--> Grouped to A value Categories and so on..., like a hierarchy.

So, Considering A and B categories will give you only upper level totals.

For eg: Region--> Country--->State-->City

If you leave State and City in Pivot, you can see sum to Region level and Country level only.

[sunny_talwar]

May be using set analysis

Dimension

X

Expression

Sum({<X = {'A', 'B'}>}Value)

or

Sum({<X -= {'C', 'D'}>}Value)

Alternatively, you can use calculated dimension

If(Match(X, 'A', 'B'), X)

or

If(Not Match(X, 'C', 'D'), X)

(With calculated dimension, remember to select 'Suppress When Value Is Null' on the dimensions tab.

Expression

Sum(Measure)

[Anonymous]

I used a simple expression like If (X = 'A' or X='B', X,0)