dynamic row in pivot table

[xingstar97]

I wanted to create a pivot table. The row of the pivot table depends on user's selection. If user select dim1 in a filter (let's say filter 1), then the row is dim1; if they select dim2, then the row is dim2. I found two ways doing this.

1. create a variable 'dim', add variable input in the custom objects, select dim as the variable, and add value 1 (label dim1) and value 2 (label dim2), then the row of the pivot table is set as "pick($(dim), dim1, dim2)".

2. load an inline table: load * inline [dim, dim1,dim2]; add a filter pane for dim, then the pivot table row is set as if(Match(GetFieldSelections(dim), 'dim'), dim1, )&if(Match(GetFieldSelections(dim), 'dim2'), dim2, ).

However, no matter which approach I use, I encountered the same issue. When I select a cell in the pivot table, it reset other filters I apply before. For example, I've already selected value1 in filter 2, when I select a cell in the pivot table, filter 2 is reset based on the selection of the cell.

I know if I don't set this dynamic row, this wouldn't happen. But I don't know why this happened in my situation, and how I should make changes. Thanks! 

[pravinboniface]

@xingstar97 Is it possible that this goes back to the basics of the associative selection model? 

When you make a selection in the pivot table, the values in the other filter are entering the excluded state, and therefore, the filter is getting reset?  Try the selections tool after selecting the cell in the pivot table to see if any values exist for the filter that got reset.  I suspect this has nothing to do with the dynamic row.

[Rohan · Accepted solution]

Hi Xing,

Instead Try the Hide & Show functionality in the Pivot once. Where you add both Dim1 & Dim2 in the pivot & put your Variable conditions in the "Show Column if" part. & check.

Regards,

Rohan.

[marcus_sommer]

Re: Multiple filters - Qlik Community - 2426843

[xingstar97]

I've already rebuilt the model, no synthetic key exists, the model now is one fact table with multiple dimension tables