topic Re: Functional filtering in Set Analysis in QlikView

Functional filtering in Set Analysis

Anonymous — Thu, 12 Mar 2015 08:30:17 GMT

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

Miguel_Angel_Baeyens — Thu, 12 Mar 2015 08:36:47 GMT

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 — Thu, 12 Mar 2015 08:42:45 GMT

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)

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

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

Anonymous — Thu, 12 Mar 2015 08:54:37 GMT

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 — Thu, 12 Mar 2015 11:00:18 GMT

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

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

Anonymous — Thu, 12 Mar 2015 11:14:39 GMT

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 — Thu, 12 Mar 2015 12:11:08 GMT

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