topic Re: Dimension linked to the selection in QlikView

Dimension linked to the selection

Wed, 07 Oct 2015 09:18:12 GMT

Hello,

How can i set a Dimension if i want that the name in the dimension change with every selection done?

For example i have the sales of a region, and i want to appear the name of the region selected, but if i do not select the region i want a full acumulation.

For example this is the data:

REGION	SALES
Andalucia	10
Madrid	20
Cataluña	20

This should be the table with no selection in Region

Dimension	SALES
REGION	50

And this is what i want if i select for example "Cataluña"

Dimension	SALES
Cataluña	20
REGION	30

Is this possible ?

Thanks

Re: Dimension linked to the selection

r3iuk — Wed, 07 Oct 2015 10:01:00 GMT

For the dimension, use:

if( Index( GetFieldSelections(REGION, '|'), REGION) > 0, REGION,'REGION')

For the expression use:

=sum({$< REGION=>}SALES)

Re: Dimension linked to the selection

sujeetsingh — Wed, 07 Oct 2015 10:25:55 GMT

You have number of ways to do this there are UI tricks which can be used like having two objects (Charts) and swaping then on selection .

Re: Dimension linked to the selection

Digvijay_Singh — Thu, 08 Oct 2015 07:52:48 GMT

Hi Mike,

I liked the solution, but frankly speaking didn't understand few technical things so thought of asking like when selecting 2 regions, the index functioning is returning 0, may be because multiple selection is giving null in REGION(QV uses 'only' when no aggregation provided?) in index function. But its not impacting the output, not sure why.

Also I thought 'if' in calculated dimension will show one of the true condition as dimension in chart('REGION' or REGION) but it is showing both as desired, not sure how it is happening technically. Is it because of disregarded dimension REGION in expression, not sure how?

P.S. I tried with concat(REGION) in index function, it resulted in 1 as index output when multiple regions are selected, but since without it also its working, just wanted to know technical stuff behind it. Thanks in advance!

Thanks,

Digvijay

Re: Dimension linked to the selection

r3iuk — Thu, 08 Oct 2015 13:27:59 GMT

The calculated dimension takes each value in the REGION dimension in turn and applies the calculation to it.

If for example If we have Andalucia & Madrid selected. The part of the calculation that is GetFieldSelections(REGION, '|') will contain the string 'Andalucia|Madrid'.

So for the dimensions Andalucia and Madrid, the function Index( GetFieldSelections(REGION, '|'), REGION) will return a positive value because each of these dimensions can be found in the string 'Andalucia|Madrid'. Therefore in these 2 cases, the IF statement is true and will return the true part, i.e. the value in the field REGION .

In the case of dimension Cataluna, this value is not in the string 'Andalucia|Madrid', therefore the Index function returns zero, and the IF statment is false so the false part is returned, i.e. the string value 'REGION'.

So in this example you end up with 2 dimensions of Andalucia and Madrid because they were selected, plus the derived dimension 'REGION' which contains the unselected dimensions (in this case just Cataluna).

The values for all the dimensions get reported in the table because we used the set analysis clause in the expression to ignore the REGION selection filtering.

Re: Dimension linked to the selection

Digvijay_Singh — Thu, 08 Oct 2015 15:20:56 GMT

Thanks for sharing the detail. Your first statement had the crux which made me comfortable understanding rest of the detail.