Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Pivot table filter measure based on %

 

 

Hello - In the example below, if I wanted to filter for CategoryName having % North America > 20%, is that possible? I was trying to see if set analysis would help this filter, but I couldn’t see how. Then I saw some other posts about filtering pivot tables, but that was based on a dimension or column. Here I need to filter a pivot table based on a measure value for a  specific column value (in this case, North America  20%) … If that makes any sense. Thanks.

sense.Pivot_Filter_Measure_2019-02-23_23-45-44.jpg

1 Solution

Accepted Solutions
Luminary
Luminary

Hi,

There two ways to filter measure based on %.

One is to use filterpane,the other is use aggr function and set Analysis in dimension.

I attached sample qvf. Please check this.

 

View solution in original post

3 Replies
Luminary
Luminary

Hi,

There two ways to filter measure based on %.

One is to use filterpane,the other is use aggr function and set Analysis in dimension.

I attached sample qvf. Please check this.

 

View solution in original post

Contributor II
Contributor II

Thanks for your feedback. Question on the .qvf: in the second pivot table, I expected the %'s to add to 100% across? They don't? In fact, I expected the %'s across for the filtered rows to be the same as what shows in the 1st pivot table above, for those same filtered rows?

Secondly, if year was an additional column (1st level column), then I assume the same set expression syntax could be applied with minor tweak below? Added "yr":

IF(
 Aggr(nodistinct
     Sum({<Division={'North America'}>}Share)/Sum({<Division={'North America'}>} total <Division, yr> Share)
 ,Category)>=0.2,
Category)

Contributor II
Contributor II

Got the sample to work by the following revised dimension. This way, the row adds to 100% across. And if I had two columns, I would add the second column separated by a comma where <Category> is listed in the expression below. Thanks for your earlier help!!

IF(
 
     AGGR(nodistinct Sum({<Division={'North America'}>}  Share)/Sum(total <Category> Share), Category)
 >=0.20,
Category)