11 Replies Latest reply: Apr 8, 2014 5:45 PM by Clark Kipling

# Current selections versus total

I am currently trying to create a dimension on a cross tab which lets me easily compare the current selection to the total across all dimensions.

Basically I have two listboxes, A and B.  A user selects item 1 from A and item 2 from B.  I want a cross tab that shows sum of X and sum of Y along the rows, with the users current selections in column 1 and the sums for all selections in column 2.  Any ideas on how I might achieve this with a calculated dimension?

• ###### Re: Current selections versus total

Hi,

Could you provide an eg or screenshot to understand the problem better. I am new but let me try as much as I can

Kiru

• ###### Re: Current selections versus total

Kiru,

Example is as follows.  Assume I load the following inline

Group,Value1,Value2

A,10,1

A,10,1

A,10,1

B,10,1

B,10,1

C,10,1

];

Now I have a Listbox where the user has selected A only.  I want a crosstab that shows the total for A (the current selections), and the total for B and C (the others) across Value1 and Value2.  If the user changes his/her selection in the Listbox, I need to update the crosstab.

In the example above, crosstab would be all like:

Current Selection | Others

Value1 |                      30  | 30

Value2 |                        3  |  3

• ###### Re: Current selections versus total

Change script to:

CROSSTABLE (ValueType,Value)

Group,Value1,Value2

A,10,1

A,10,1

A,10,1

B,10,1

B,10,1

C,10,1

];

Then add chart with Dimension of ValueType and these expressions:

=SUM(Value)

=SUM({1-\$}Value)

• ###### Re: Current selections versus total

Clark - Sorry I cannot change the script.  It would not perform and there are many computations which require the script to be the way it is + for end user usability.

• ###### Re: Current selections versus total

I am sure there is a more elegant way to do it (personally I would opt for the script change), but you can try a calculated dimension similar to the following:

=IF(MATCH(Group,GetFieldSelections(Group,','))=1,'Current','Others')

With an expressions of

=SUM({<Group=>}Value1)

=SUM({<Group=>}Value2)

I am pretty sure this won't perform well over large data sets.

• ###### Re: Current selections versus total

Dear Matthew,

PFA, Hope will helps you

• ###### Re: Current selections versus total

Hi Raj,

No this does not help.  As discussed, I cannot use the Crosstable facility.  Data must be in the exact format I proscribed.

• ###### Re: Current selections versus total

Hi Matthew,

Can't you change the appearance of the chart though?

some thg like

Value1 ,value 2 in dimensions and use the above set analysis in 4 diff expressions to show the selected and other values?

• ###### Re: Current selections versus total

What would be preferred is to make a Dimension with Members "Current Selections" and "Others".  COGNOS lets me do this.  Why not QV?

• ###### Re: Re: Current selections versus total

Hi mathew,

There is nothing cognos can do and qlikview can't do.

Try the attached document.

Suraj