topic Re: Expand a Pivot Table to a certain Level with a Macro? in QlikView

Expand a Pivot Table to a certain Level with a Macro?

Thu, 13 Dec 2012 07:37:52 GMT

Hi All,

unfortunately i wasn't successful with finding an answer to this by searching in here and the documentation, so I need to ask you... Is there a known way to expand a pivot table to a certain level using a macro? For example a table with maybe 5 dimensions is fully expanded, but when the macro runs, it should be collapsed/changed so only the first 2 dimensions are expanded. The same of course when the table is fully collapsed... after the macro also the first 2 dimensions should be expanded.

Thanks a lot in advance,

Martin

Re: Expand a Pivot Table to a certain Level with a Macro?

nirav_bhimani — Thu, 13 Dec 2012 08:01:07 GMT

Hi,

I dont know about macro but following code will help you for dynamic pivot table based on level of selection in list box.

I have created 5 levels in pivot. This figure we can vary based on levels selection.

1. Write inline table in the load script.

Levels:
LOAD * INLINE [
Levels
Level1
Level2
Level3
Level4
Level5
];

// here you have to give name to each filed , database name of field or script.

Linkl:
load * Inline [
GroupFieldname,GroupField
Brandcode,BCode
Item Category Code,ICode
Product Group Code,PCode
Outlet Code,OCode
Item No,INo
];

2. In the Setting->Document Setting->General->Alternate State->Add

Grp1 ………………… Grp5

3. In the Variable define:

vlevel1 = only ( {Grp1} GroupField ) ……………… vlevel5 = only ( {Grp5} GroupField )

4. In the sheet Add Container

4.1 In the List box Set property for each level as Always one selected and

Title : Level1

Field : GroupField

Alternate State: Grp1…………………………….. Similarly 4 others as well.

4.2 Add listbox : Levels

5. Add Pivot Table -> Go to Dimension :

5.1 Add Calculated Dimension:

=$(vlevel1) …………………… =$(vlevel5)

5.2 Checked Enable Condition Checkbox

Match('Level1',$(vlevelvarchar))>0 …………………… Similarly 4 others as well.

5.3 Checked Label

=only({Grp1}GroupFieldname) ……………….………… Similarly 4 others as well.

Hope this will help you.

Regads,

Nirav Bhimani

Re: Expand a Pivot Table to a certain Level with a Macro?

Thu, 13 Dec 2012 08:28:47 GMT

Dear Nirav,

I am afraid this won't solve my problem but nevertheless it is an amazing piece of code, thank you, I am sure I will need it soon!!

Regards,

Martin

Re: Expand a Pivot Table to a certain Level with a Macro?

Tue, 18 Dec 2012 15:13:11 GMT

Hi again,

no one ever had that problem?

Cheers,

Martin

Re: Expand a Pivot Table to a certain Level with a Macro?

flipside — Tue, 18 Dec 2012 15:47:21 GMT

You can use bookmarks with the Include Layout State option selected, so create a bookmark for the levels of expansion you need (without any selections) and apply the bookmark through actions via a button or similar.

flipside

Re: Expand a Pivot Table to a certain Level with a Macro?

Thu, 13 Jun 2013 04:07:58 GMT

how i create bookmarks for the levels of expansion