
1. Re: Help with filtering
T G Sep 12, 2017 2:54 AM (in response to kristian.jarvenpaa)Hello Kristian,
What is the expected result.
TG

2. Re: Help with filtering
kristian.jarvenpaa Sep 12, 2017 4:40 AM (in response to T G)Hi T.G
The expected result would be
Row
ID
Product
Sum
2 2 B 43 3 2 A 23 4 2 A 443 5 2 C 32 From this I could, for example, add all sums together. This would answer the question: What is the total sales (Sum) of companies (ID) who have both product A and B

3. Re: Help with filtering
T G Sep 12, 2017 5:01 AM (in response to kristian.jarvenpaa)Hello,
Please refer my attached solution.
TG

Sample Workbook.twbx 10.7 KB


4. Re: Help with filtering
kristian.jarvenpaa Sep 12, 2017 5:57 AM (in response to T G)Hi
Thanks for your suggestion!
Problem here is that you are making assumption about the number of rows per IDs. I was unclear about this before but there could be 10 000 rows which have ID = 1 we can't make any assumption about that. Maybe I should have used a bit more exact example.
What I'm looking for, is a method to filter IDs based on Products. My intuition would be the following
1. Make a set of all IDs that have a row containing product A and a row containing product B
2. Use this set to filter out all IDs not included in this set.
However, I'm not able to do this without also filtering away rows that don't include product A or product B e.g in my example above I always end up filtering away row 5.

5. Re: Help with filtering
Ritesh Bisht Sep 12, 2017 10:31 PM (in response to kristian.jarvenpaa)Hi Kritian,
Have not checked the other solution but if you have only 2 products here then a sort of short trick can work.
Give some codes to product A & B
Step 1
A & B=
if [Product]='A' THEN 1000 ELSEIF [Product]='B' THEN .0001 ELSE 0 END
Step 2
Sum of A & B
{ FIXED [ID]:SUM([A & B])}
Step 3
Here we know that any ID having A & B should have sum value > 1000 with a decimal (.)
Check for=
CONTAINS(STR([Sum of Calc A & B]),'.') and [Sum of Calc A & B] > 1000
NOTE : There can be .0001 adding upto 1000 or say 1000/.0001=10000000 times , I hope you don't have that many entries for a particular ID,
Step 4
Drag ""Check for"" to filter and select TRUE
Thanks,
Ritesh

6. Re: Help with filtering
Kalyan Allam Sep 12, 2017 11:16 PM (in response to kristian.jarvenpaa)I think sets can be used here. But you need to specify a proper condition for that. Here is my condition for Sets.
I made the sets on IDs .
and for Set B
and then i made a combined Set for IDs containing both A and B.
I am also attaching the workbook. Please let me know if it helps.
Thanks,
Kalyan Allam

Filtering with Sets.twbx 11.4 KB


7. Re: Help with filtering
kristian.jarvenpaa Sep 12, 2017 11:43 PM (in response to Kalyan Allam)Thanks! This is exactly what I needed

8. Re: Help with filtering
kristian.jarvenpaa Sep 12, 2017 11:45 PM (in response to Ritesh Bisht)Thank you RItesh! This would have worked also but I found Kalyan's solutions to be more intuitive.

9. Re: Help with filtering
Ritesh Bisht Sep 12, 2017 11:52 PM (in response to kristian.jarvenpaa)I agree

10. Re: Help with filtering
Kalyan Allam Sep 13, 2017 4:24 AM (in response to kristian.jarvenpaa)you are welcome