topic calculated dimension in QlikView

calculated dimension

Anonymous — Mon, 19 Sep 2016 21:15:07 GMT

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

Re: calculated dimension

el_aprendiz111 — Mon, 19 Sep 2016 21:39:31 GMT

Hi,

Re: calculated dimension

Anonymous — Mon, 19 Sep 2016 21:44:47 GMT

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.

Re: calculated dimension

sunny_talwar — Tue, 20 Sep 2016 00:53:04 GMT

May be using set analysis

Dimension

Expression

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

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

Alternatively, you can use calculated dimension

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

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)

Re: calculated dimension

Anonymous — Wed, 21 Sep 2016 17:05:17 GMT

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