Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fionagil
Contributor
Contributor

Functional filtering in Set Analysis

Is it possible to filter on sum(dimension) or count(dimension) inside a set analysis?

6 Replies
Miguel_Angel_Baeyens

Hi Fiona,

You can use an aggregation expression or any other valid QlikView expression for that matter in set analysis, for example

Sum({< CustomerID = {"=Sum({1< Month = {201503} >} Sales) > 10000"} >}) NetMargin)

Will return the sum of NetMargin only for those customers who had have sales above 10000 in March, 2015.

Is that what you are looking for?

Miguel

MK_QSL
MVP
MVP

Consider that you want to count the customers whose sales exceed 1000.. You can use below in Text Box...


COUNT(Distinct {<Customer = {"=SUM(Sales)>1000"}>}Customer)

or

COUNT(Distinct {<Customer = {"=SUM({1}Sales)>1000"}>}Customer)

or

COUNT(Distinct {1<Customer = {"=SUM(Sales)>1000"}>}Customer)

fionagil
Contributor
Contributor
Author

Great thanks. How about when I want to count the number of distinct IDs? e.g. they have at least 4 records..

It's within an aggregate function.

e.g.

aggr(only({<Customer={"=Count(Distinct(RecordID))>4"}>}Customer),Customer)

I tried something like this and it didn't work...

MK_QSL
MVP
MVP

=COUNT({<Customer = {"=COUNT(Distinct(RecordID))>=4"}>}DISTINCT Customer)

No Need of AGGR but you can try below also

=COUNT(Aggr(COUNT({<Customer = {"=COUNT(Distinct(RecordID))>=4"}>}DISTINCT Customer),Customer))

or

=SUM(Aggr(COUNT({<Customer = {"=COUNT(Distinct(RecordID))>=4"}>}DISTINCT Customer),Customer))

fionagil
Contributor
Contributor
Author

The expression is in a chart which I believe is why the aggregate is there...

Does it work without outputting a count? I'm trying to apply it to a rank function...

aggr(only({<CustomerName={"=rank( sum({<CustomerName-={NULL}, Store={'10'}>}),4)<2"}>} CustomerName), CustomerName)

And I only want to include the customers in this ranking where the number of Record IDs is greater than 4 (i.e. count(distinct(RecordID))>4)

Thanks

MK_QSL
MVP
MVP

Let me have the sample database and also your expected output.. I will try this for you.