Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm a self made user of QV11 and I have a question about how to hide a dimension of a chart.
Here is the situation :
I'm loading my data from an excel file.
In order to custom the sorting order of my chart, I "left join" another excel file with the sorting order values (1, 2, 3, etc...)
For the data not matching with the sorting order file, I map the value 999.
So to illustrate, I have :
Data file :
[item]
A
B
C
D
Sorting file :
[item],[sort order]
A,3
B,1
C,2
Which gives me the table after mapping :
[item],[sort order]
A,3
B,1
C,2
D,999
(plus all the values in the data files)
So in my chart, I show the different items as dimension, the values I want to show and I sort my items using the expression [sort order].
I then have showed :
B, C, A, D
That works perfectly but now I want to have the possibility to hide all the dimensions not present in my sorting file (so all the dimensions having 999 in [sort oder]).
If possible, I would like to use a kind of switch button "show/hide".
Do you know how I could do it ?
Marc
There is two ways you can do this:
1) Calculated dimension (as Rob also mentioned)
Dimension
=If(vVar = 1, If(Sort <> 999, Item), Item)
Expression
=Sum(Sales)
2) Set analysis
Dimension:
Item
Expression:
=Sum({<Sort = {$(=If(vVar = 1, Chr(39) & '>999<999' & Chr(39), Chr(39) & '*' & Chr(39)))}>}Sales)
A simple approach is to create the Dimension twice. Once as [item], the second with the expression
=aggr(only({<[sort order]-={999}>}item), item)
Use the Enable Conditional property on each Dim to test a variable. eg =vShow999 and =NOT vShow999
Create the variable and initialize it to 1. Then create a button that changes the variable value.
-Rob
There is two ways you can do this:
1) Calculated dimension (as Rob also mentioned)
Dimension
=If(vVar = 1, If(Sort <> 999, Item), Item)
Expression
=Sum(Sales)
2) Set analysis
Dimension:
Item
Expression:
=Sum({<Sort = {$(=If(vVar = 1, Chr(39) & '>999<999' & Chr(39), Chr(39) & '*' & Chr(39)))}>}Sales)
Thanks to both of you, that works perfectly.
I used the solution 1 from Sunny T, which is very simple.
I spent several days playing with the property "Activate condition" and tried to use quite the same formula but seems I had to use a calulated dimension instead.
Hi,
maybe another solution could be to create an additional selector field and simply use a listbox instead of a button:
mapSortingFile:
Mapping LOAD * Inline [
item,sort order
A,3
B,1
C,2
];
tabDataFile:
LOAD *,
Ceil(Rand()*100) as SomeFact,
ApplyMap('mapSortingFile',item,999) as [sort order],
If(ApplyMap('mapSortingFile',item,Null()),'only items present in sorting table') as [show/hide]
Inline [
item
A
B
C
D
];
hope this helps
regards
Marco
Hi Marco,
Interesting solution too.
Good solutions,
I have a problem like that, and now I can solve it!
Thanks all!