Hide dimensions in charts

[takama13]

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

[rwunderlich]

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

http://masterssummit.com

http://qlikviewcookbook.com

[sunny_talwar · Accepted solution]

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)

[takama13]

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.

[MarcoWedel]

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

[takama13]

Hi Marco,

Interesting solution too.

[gilbertomedeiro]

Good solutions,

I have a problem like that, and now I can solve it!

Thanks all!