Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
Dimensions
Date
=If(Aggr(Count(DISTINCT {<Girlfriend = {0}, Distance = {'<=5'}>} Boy), Date, Boy) = 1, 'BAD', 'GOOD')
Expression
Count(DISTINCT Boy)
Try this as your second dimension
Aggr(If(Count({<Girlfriend = {0}, Distance={'<=5'}>} Boy) >= 1,'BAD','GOOD'), [time stamp], Boy)
Doesn't work. the chart show only 'BAD'...
Would you be able to share a sample and the expected output to check this out?
My bad, you already did share a sample... testing it out now
I guess the timestamp field is missing... can you provide data which includes timestamp?
Date | Boy | Girl | Girlfriend | Distance |
---|---|---|---|---|
07/06/2017 | Joe | Jenny | 0 | 4 |
07/06/2017 | Joe | Rebecca | 1 | 2 |
07/06/2017 | Joe | Monica | 0 | 7 |
07/06/2017 | Joe | Sofia | 0 | 1 |
07/06/2017 | Billy | Nikka | 0 | 7 |
07/06/2017 | Billy | Rachel | 1 | 8 |
08/06/2017 | Joe | Rebecca | 1 | 7 |
08/06/2017 | Joe | Jenny | 0 | 9 |
08/06/2017 | Joe | Monica | 0 | 6 |
08/06/2017 | Billy | Rachel | 1 | 3 |
08/06/2017 | Billy | Sofia | 0 | 4 |
08/06/2017 | Billy | Rebecca | 0 | 10 |
09/06/2017 | Joe | Rebecca | 1 | 3 |
09/06/2017 | Joe | Rachel | 0 | 9 |
09/06/2017 | Billy | Rachel | 1 | 2 |
09/06/2017 | Billy | Nikka | 0 | 7 |
10/06/2017 | Joe | Rebecca | 1 | 1 |
10/06/2017 | Joe | Sofia | 0 | 2 |
10/06/2017 | Joe | Monica | 0 | 3 |
The expected result:
07/06/2017 - 1 'BAD' (because Jenny and Sofia for Joe) and 1 'GOOD' (for Billy)
08/06/2017 - 1 'GOOD (for Joe) and 1 'BAD' (because Sofia for Billy)
09/06/2017 - 2 'GOOD' (for Joe and Billy)
10/06/2017 - 1 'BAD' (because Sofia and Monica for Joe)
Try this
Dimensions
Date
=If(Aggr(Count(DISTINCT {<Girlfriend = {0}, Distance = {'<=5'}>} Boy), Date, Boy) = 1, 'BAD', 'GOOD')
Expression
Count(DISTINCT Boy)
I'm going to try, but on your solution at 09/07/2017 there is only 1 'GOOD' while the result should be 2 'GOOD's (1 for Joe and 1 for Billy)...
I am seeing a count of 2 for good?