Changing visibility of chart

[jduenyas]

Hi All

I have a chart with:

Chart Type – Bar Chart

Dimensions –    Month

                          Dept

Expression – Sum(Total)

I have Checked-In the Invisible checkbox for the expression Sum(Total) so the Bars of the chart will not show and I have Checked-In the Linear in the Trend-lines.

Since the Month dimension is first, the Months’ values are on the X axis and the Departments legend are listed vertically on the right.

That results in a view of only the Trend-lines of each department over the months. (Since there are more than 5-6 departments the view tend to be cluttered when the bars and the trendlines are all viewable)

I wish to view the Bars which constitute the linear trend-line when a single department is selected.

I assume it needs to be done in VB code in the Edit Module. (can it be done otherwise?)

Does anyone know how?

Thanks in advance.

Josh

PS I attached a sample application that will help understand by dilemma

[#2]

hi ,

can you check this & let me know if it's a solution 4 ur problem ...

if not let me know where i'm getting wrong

thanks

Meher

[jduenyas]

Nice solution!

Make a linked chart and make one conditionally disappear when single Dept is selected and vice versa.

There's got to be a way though to change that via code.

I like your approach nonetheless.

Thanks

[#4]

Yes, you can do it with macro , but i'm not  a expert in VB scripting & i don't prefer macro's too which are not good in standards of performance & sometimes they fails too (in ZFP)  ...

thank you

Meher

[#5 · Accepted solution]

That's indeed a creative solution.  If you would rather do with code and use a single chart, here's how you can do so:

Setup a trigger on the DEPT field (Settings | Document Properties | Triggers, Field Event Triggers, select Dept, add an Action for "OnSelect".  Action should be External / Run Macro, then edit the module and create the following macro sub:

Sub SetTrendVisibilityByDept

' get value of field DEPT
Set valDept = ActiveDocument.Fields("Dept").GetSelectedValues

Set obj = ActiveDocument.GetSheetObject("CH01")
Set objProp = obj.GetProperties
Set objExpr = objProp.Expressions.Item(0).Item(0).Data.ExpressionVisual

' toggle invisible property of chart expression
' based on value of DEPT field selection
if valDept.Count = 1 then
objExpr.Invisible = False
Else
objExpr.Invisible = True
end if
obj.SetProperties objProp

End Sub

Finally, be sure to set the name of the sub as the Action for the triggger (SetTrendVisibilityByDept).

This macro assumes your chart object is CH01, and that the first dimension is to be changed (the first .Item(0) sub is the dimension).  Also, the bars appear only if you select ONE department (thus the code:  If valDept.Count = 1).  If you want the bars on any number of Depts, change to If valDept.Count > 1.  When the user clears the selections, the value for valDept.Count will = 0.

I 've attached a sample of your solution with this code in place.  Make a select for Dept. and you see the bars, clear it and they go away.

[#6]

Wow ,

thats really a nice peice of code 

Meher

[jduenyas]

Thanks!

From one coder to another

Exactly what I was looking for.

I am an expert programmer of Access nut not too familiar with the properties and methods of QlikView.

[jduenyas]

What or why the consecutive item(0) in

Set objExpr = objProp.Expressions.Item(0).Item(0).Data.ExpressionVisual

Since there is no Intellisense with the Editor where are the properties and methods of the different objects?

Thanks

[#9]

The first Item(n) is the collection representing the Expressions, and it's 0-based (so Item(0) is the first expression).  If you were looking instead at Dimensions, then it would be the collection of dimensions (and in this case, you would reference the .ExpressionData object instead of .ExpressionVisual.

As for the apparent collection under this collection (thus the next .Item(0)), I have not found documentation or been able to resolve why that's a collection.  I examined the docs I could find, and exported the chart to XML in order to study, but it appears it's always just ONE member of this sub-collection (whether it be ExpressionData or ExpressionVisual).  Perhaps QlikView is storing other members in this collection, but it seems all of the common properties are exposed under this first member.

The API Guide for QlikView may be downloaded from their site (Downloads section), which provides a lot of help on the object model.  I'd like to see more, but it's a great place to start.

[jduenyas]

Much appreciated!

Thanks