topic Re: Automatic Button in Qlik Learning Discussions

Automatic Button

Tue, 03 Mar 2015 15:41:35 GMT

Hi,

Does anyone know how can I do something automatic in qlikview? It is that

In pivot table, you know there are dimensions hided in + and we expand it all to be able to see each dimension further. My question is is there a way to do it automatically expanded in a simple button instead of doing it manually? Maybe in macro or similar...?

Many thanks in advance,

Cheers

Kezban

Re: Automatic Button

awhitfield — Tue, 03 Mar 2015 15:44:49 GMT

Hi Kezban,

See the following post:

http://community.qlik.com/thread/102959

Re: Automatic Button

Wed, 04 Mar 2015 07:32:45 GMT

Hi Andrew,

I am very thankful and grateful for your prompt response. Thank you.

Cheers

Kezban

Re: Automatic Button

awhitfield — Wed, 04 Mar 2015 09:01:00 GMT

Hi Kezban, it this was the correct solution, can you please marked the post as answered?

Thanks

Andy

Re: Automatic Button

Thu, 05 Mar 2015 15:27:36 GMT

Hi Andrew ,

I am thankful for the code I have received about pivot expanding and collapsing macro. However , I want to make it run when i qlik on to button to expand and for another button when i qlick it to collapse. Qlikview does not understand it or maybe I dont know how to do it. Do you have any advice for it?

Many thanks,

Cheers

Re: Automatic Button

awhitfield — Thu, 05 Mar 2015 16:21:38 GMT

There is another neat why of doing it without macros, see the pivot charts on the "Conditional(Dynamic) Columns" sheet of the attached qvw

Re: Automatic Button

Fri, 06 Mar 2015 09:02:11 GMT

Hi Dear Andrew,

I am more than grateful for your response with very helpful tutorial. It works perfect in my project now. It expands all and collapse all. That is perfect. However, the only thing I wanna do that when I qlik the button to collapse, the pluses ( + ) dissepear. I really want them to stay and just expand and collapsed. Now they dissepear and I lose my will to select if I dont wanna use the button. There might be other way like running macro but I may not know some details to work like with + s. In this project I got, would be nice if I would be able to work it well and could still see pluses.

Would you have any advice for that?

Many thanks, Feeling lucky to have that professional environment in here as I'm kinda newbie for qlikview.

Cheers

Kezban

Re: Automatic Button

awhitfield — Fri, 06 Mar 2015 09:08:52 GMT

Hi Kezab,

do you know that if you right click on a Pivot table, you have the option to 'Expand All' / 'Collapse All', by default?

Re: Automatic Button

Fri, 06 Mar 2015 09:12:31 GMT

Andrew,

I do know it. But our customer doesnt want to do that. The reason I want it quick and automatic by using button or qlik. The customer should be able to also see the plusses. But they dissepear. (I mean I work the section in the qw file u sent is "Conditional (dynamic) Columns" last section of pivot table.

Re: Automatic Button

awhitfield — Fri, 06 Mar 2015 09:26:34 GMT

Hi Kezban,

this is a better macro:

Sub ExpandChart
'This macro fully expands the selected pivot table.
On Error Resume Next

Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")
Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)
Set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = TRUE 'not gp.TableProperties.PivotAlwaysFullyExpanded
chart.SetProperties gp
End Sub

Sub CollapseChart
'This macro allowse collapsing of the selected pivot table.
On Error Resume Next

Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")
Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)
Set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = FALSE 'not gp.TableProperties.PivotAlwaysFullyExpanded
chart.SetProperties gp
End Sub

You need to create a variable vChartToExpand that contains the chart ID and 2 buttons, one for expand and one for collapse, you will see the +/- when the chart is Collapsed, but NOT when its fully expanded, there's no way round this..

Re: Automatic Button

Fri, 06 Mar 2015 11:22:05 GMT

Hi Andrew,

I just would like to sum it up below:

I have 2 sub expand and sub collapse macros. So I will write them down in a action inside the button with run macro section. Right? I hope that should be enough. Becuse ,it works all together it doesnt understand when collpase when to expand. I have good knowledge of excel macros but not the same in qlikview. Then I have doubts on variable:

vChartToExpand : what should i type inside in expression of this variable? You said chart ID 2 buttons. The only thing I am stuck on now.

Many thanks...

Kezban

Re: Automatic Button

awhitfield — Fri, 06 Mar 2015 11:32:53 GMT

No problem Kezban, I a created an example QVW as described above

Re: Automatic Button

Fri, 06 Mar 2015 14:16:41 GMT

Hi Andrew,

Thank you a lot for your prompt very helpful responses. I am more than grateful as It helped a lot for my job through the customer. I am like 1 month in qlikview getting into it.

thnks again

Cheers

Kezban