Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
greend21
Creator III
Creator III

Make selection without altering pivot table layout

I know the native response in Qlik Sense is that if you have a dimension with four values in a pivot table and a filter is applied to select one of them, the entire pivot will change to only show that value within that dimension.  Is there a way, whether with the native pivot table or an extension, to have this so the other dimension values would also remain but would just change to 0 or null in the measure?

For example, if you have Category (A,B,C,D) as your columns and you select category B then only that one will show and the others will no longer be visible within the pivot. A requirement for an upcoming project is to have A,C, and D still remain but with 0's or null values. Thanks!

Labels (2)
2 Solutions

Accepted Solutions
Rodj
Luminary Alumni
Luminary Alumni

Sure, it's achievable. Usually I do it by accident when I don't intend to but I had to think about how to do it deliberately! 

Say I am trying to sum Rev in the way you have suggested. In my measure I would put:

if(GetFieldSelections(Category) = Category, Sum({1} Rev),0)

This will result in 0 values for the unselected Category values.

View solution in original post

greend21
Creator III
Creator III
Author

I had to wrap it in "IF(GetSelectedCount(Category) = 0" or else the sums were always 0 until a selection was made but this definitely helped. Thanks!

Any idea why this formula throws off my sorting though? I can either sort using Match() or a sort ID but as soon as I make a selection it gets unsorted.

View solution in original post

3 Replies
Rodj
Luminary Alumni
Luminary Alumni

Sure, it's achievable. Usually I do it by accident when I don't intend to but I had to think about how to do it deliberately! 

Say I am trying to sum Rev in the way you have suggested. In my measure I would put:

if(GetFieldSelections(Category) = Category, Sum({1} Rev),0)

This will result in 0 values for the unselected Category values.

greend21
Creator III
Creator III
Author

I had to wrap it in "IF(GetSelectedCount(Category) = 0" or else the sums were always 0 until a selection was made but this definitely helped. Thanks!

Any idea why this formula throws off my sorting though? I can either sort using Match() or a sort ID but as soon as I make a selection it gets unsorted.

Rodj
Luminary Alumni
Luminary Alumni

I'd guess that the sorting isn't taking the set expression into account and only utilising the standard selections, It's not something I've played with before so I'm not sure on what options you'll have with that.