Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is it possible to filter on sum(dimension) or count(dimension) inside a set analysis?
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
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)
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...
=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))
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
Let me have the sample database and also your expected output.. I will try this for you.