Filtering out in multi-level pivot table

[user467341]

Hi all,

I have the following pivot table:

I will like to create an action button, or a "filter pane" like object that can filter the type. The idea is that on default, the pivot table will only show the default type, for example "Internal" upon load. Then the end-user can click on the action button or "filter pane" object to hide or unhide the selected types.

As an example, if if I click on the button to hide "External", then the table will become like this:

I tried to use variable to create variable for External, Internal and Others and assigning a number to these (1,2,3). Then I tried to use add a IF statement in the "Show column if" for the Type dimension setting but it will hide the whole column.

Is this actually achievable? Thanks.

PS: This will be like filtering the table using the normal filter pane, but instead I do not want it to be sheet wide (this needs to be within this pivot table only)

[neerajthakur]

Hi, in measure do this using variable you created

if($(vVariable)='Internal',Count({<Type={'Internal'}>} ID),

if($(vVariable)='External',Count({<Type={'External'}>} ID))

and then from Data Handling uncheck show zero values

Create a Variable Input with Presentation as Buttons and Set Values 1 label as Internal, 2 as External like this as you want. By clicking on this button users can filter data accordingly.

[user467341]

Hi Neeraj, thanks for your reply. I am abit confused.

The IF is to put in my measure columns? Currently I have an existing set expression in my measure column - IF([DS]<>'Outdated', sum(Price)). What is ID supposed to be? Additionally, in my vVariable setup, should I set my definition to number or "Internal", "External"?

For the button, will it be a IF expression in the "Values" part? Appreciate if you have an example code.

[neerajthakur · Accepted solution]

Put if before your existing expressions like this

if($(vVariable)=1,

IF([DS]<>'Outdated', sum({<Type={'Internal'}>}Price),

if($(vVariable)=2,

IF([DS]<>'Outdated', sum({<Type={'External'}>}Price))

)

[user467341]

Thanks Neeraj, could you suggest how I can setup my buttons?

---

@neerajthakur wrote:

Create a Variable Input with Presentation as Buttons and Set Values 1 label as Internal, 2 as External like this as you want. By clicking on this button users can filter data accordingly.

---

In button "Action and Navigation", should I put the IF in both "Variable" and "Values" expression fields? How should I roughly write this out?

[neerajthakur]

After inserting it in sheet

You can use any variable you like, I am using vVariable and then giving two values 1 for Internal and 2 for External.

[user467341]

Thanks Neeraj, I am actually using the default Button object from Qlik Sense chart. For your method to work, do I need to use this Variable Inputs?

[neerajthakur]

You can use that too.

Using Variable Input it is much easier to edit it, than buttons.