Functional filtering in Set Analysis

[Anonymous]

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

[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]

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)

[Anonymous]

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]

=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))

[Anonymous]

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]

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