Challenging Condition/Formula

[aviadbein]

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

[aviadbein]

Great man! it's seems to be working.

one more question: Now, if I want to display the data on a bar chart and set the colors  with expression

I can't use: Pick(match('GOOD','BAD'),green(),red()) because i don't have a field name in the Match() func. so what can I do in order to color the 'GOOD'/'BAD' bars?

[sunny_talwar]

did you try color by expression with this expression

=If(Aggr(Count(DISTINCT {<Girlfriend = {0}, Distance = {'<=5'}>} Boy), Date, Boy) = 1, Red(), Green())

[aviadbein]

Yes, I solved it like you. It works!

Thanks a lot for all your help!

[sunny_talwar]

Awesome!!

Please consider marking any helpful responses from this thread so that it can help future visitors to this thread.

Best,

Sunny