topic Re: Current selections versus total in QlikView

Current selections versus total

Wed, 19 Mar 2014 22:02:38 GMT

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?

Thanks in advance!

Re: Current selections versus total

Tue, 08 Apr 2014 19:46:23 GMT

Can somebody please help? Starting to really need an answer here.

Re: Current selections versus total

Tue, 08 Apr 2014 20:01:26 GMT

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

Tue, 08 Apr 2014 20:11:00 GMT

Kiru,

Example is as follows. Assume I load the following inline

LOAD * INLINE [

Group,Value1,Value2

A,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

Tue, 08 Apr 2014 20:31:42 GMT

Change script to:

CROSSTABLE (ValueType,Value)

LOAD * INLINE [

Group,Value1,Value2

A,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

Tue, 08 Apr 2014 20:39:29 GMT

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

Tue, 08 Apr 2014 20:51:07 GMT

Dear Matthew,

PFA, Hope will helps you

Re: Current selections versus total

Tue, 08 Apr 2014 20:54:09 GMT

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

Tue, 08 Apr 2014 21:03:46 GMT

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

Tue, 08 Apr 2014 21:15:30 GMT

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

Tue, 08 Apr 2014 21:35:48 GMT

Hi mathew,

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

Try the attached document.

Suraj

Re: Current selections versus total

Tue, 08 Apr 2014 21:45:33 GMT

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.