Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
For the dimension, use:
if( Index( GetFieldSelections(REGION, '|'), REGION) > 0, REGION,'REGION')
For the expression use:
=sum({$< REGION=>}SALES)
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 .
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
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.
Thanks for sharing the detail. Your first statement had the crux which made me comfortable understanding rest of the detail.