Changing Chart dimension with a macro

[#1]

Hello,

I am using a cyclic group as a dimension in a qlikview chart. Clicking on that cylic group I can show data for three different metrics on the X-Axis.

My question is if there is some way in which it is possible to use a a cyclic group in the expression column - i.e. if I can create a sum(category1), count(catergoy2) into a group which can i use that as an expression? (by default cyclic variables dont show up in the expressions pull down lists).

Can a similar effect be acheived through the use of macros? Can I dynamically add/delete/change the expressions in the chart?

[#2 · Accepted solution]

Hello badarf,

You can write something like:

sub ChangeTimeGrain
 set ch = ActiveDocument.GetSheetObject( "CH285" )
 ch.RemoveDimension( 0 )
 ch.AddDimension( ActiveDocument.Variables( "vTimeGrain" ).GetContent.String )
end sub

Here, CH285 is the ID of the chart whose dimension is to be changed. vTimeGrain is controlled by an Input Box that lets the user select only the allowed dimension names.

A similar macro code can be written for changing / adding expressions (formulas). See APIguide.qvw for a detailed documentation.

Hope this helps,

Hellmar

[#3]

P. S. It is also possible to create Formula Groups. This is done in the formula editor for a chart.

[#4]

Thank you Hellmar. That worked just fine. I somehow missed teh APIguide.qvw before.

[#5]

Hi Both

I know this one has already been answered but I just thought I'd put my thoughts in here.

I have used the macro functions to dynamically add/remove dimensions before and I got into all sorts of problems with it, whilst the adding/removing always worked it would often start to put the dimensions on the right side of the table which is impossible in normal operations.

If the question is simply to allow cyclic expressions then this can be achieved using the group button in the expressions tab, this will produce a "cycle" expression container (but remember, this is not a cyclic group in the ture sense).

If my assumption is not right then it is still possible to change the expression according to the value of the cyclic dimension, just by creating an expression such as:

=IF(GetCurrentField("cyclic_group_name") = "Field Name 1" , expression 1, IF(GetCurrentField("cyclic_group_name") = "Field name 2" , expression 2 , expression 3 ) )

We make very heavy use of macros here, but the advice from QlikTech is to avoid them if possible, which is what I try (and often fail) to do.

Sorry about jumping in at the last minute.