Hi all,
I need your help to phrase a generic condition/formula.
Because my data is very complicated i'll simplify it with a silly example.
let's say I have the following table:
| Boy | Girl | Girlfriend (Y/N) | Distance (Miles) |
|---|
| Joe | Jenny | 0 | 4 |
| Joe | Rebecca | 1 | 2 |
| Joe | Monica | 0 | 7 |
| Billy | Nikka | 0 | 7 |
| Billy | Rachel | 1 | 8 |
My condition is:
for each Boy I want to check the distance only from Girls that NOT Girlfriend. if one or more of the different distances is less then 5 the result is 'BAD', else the result is 'GOOD'. thereafter, for each time stamp I want to count the number of 'GOOD's and 'BAD's (the total should be equal to the number of Boys in each time stamp).
According the data, for Joe we'll check the distance from Jenny and Monica and the result will be 'BAD' (because the distance from Jenny) and for Billy we'll check the distance only from Nikka and the result will be 'GOOD'.
I tried something like this:
Dimensions:
1. time stamp
2. if(count({<Girlfriend={'0'},Distance={"<=num('5')"}>}Boy)>=1,'BAD','GOOD')
Measure:
count(Boy)
and of course it didn't work.
Now, what should be the right formula/condition?
Thanks in advance,
Aviad
