13 Replies Latest reply: Jun 8, 2017 2:23 PM by Sunny Talwar

# Challenging Condition/Formula

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:

BoyGirlGirlfriend (Y/N)Distance (Miles)
JoeJenny04
JoeRebecca12
JoeMonica07
BillyNikka07
BillyRachel18

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

Measure:

count(Boy)

and of course it didn't work.

Now, what should be the right formula/condition?

• ###### Re: Challenging Condition/Formula

Try this as your second dimension

Aggr(If(Count({<Girlfriend = {0}, Distance={'<=5'}>} Boy) >= 1,'BAD','GOOD'), [time stamp], Boy)

• ###### Re: Challenging Condition/Formula

Doesn't work. the chart show only 'BAD'...

• ###### Re: Challenging Condition/Formula

Would you be able to share a sample and the expected output to check this out?

• ###### Re: Challenging Condition/Formula

My bad, you already did share a sample... testing it out now

• ###### Re: Challenging Condition/Formula

I guess the timestamp field is missing... can you provide data which includes timestamp?

• ###### Re: Challenging Condition/Formula
DateBoyGirlGirlfriendDistance
07/06/2017JoeJenny04
07/06/2017JoeRebecca12
07/06/2017JoeMonica07
07/06/2017JoeSofia01
07/06/2017BillyNikka07
07/06/2017BillyRachel18
08/06/2017JoeRebecca17
08/06/2017JoeJenny09
08/06/2017JoeMonica06
08/06/2017BillyRachel13
08/06/2017BillySofia04
08/06/2017BillyRebecca010
09/06/2017JoeRebecca13
09/06/2017JoeRachel09
09/06/2017BillyRachel12
09/06/2017BillyNikka07
10/06/2017JoeRebecca11
10/06/2017JoeSofia02
10/06/2017JoeMonica03

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)

• ###### Re: Challenging Condition/Formula

Try this

Dimensions

Date

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

Expression

Count(DISTINCT Boy)

• ###### Re: Challenging Condition/Formula

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)...

• ###### Re: Challenging Condition/Formula

I am seeing a count of 2 for good?

• ###### Re: Challenging Condition/Formula

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?

• ###### Re: Challenging Condition/Formula

did you try color by expression with this expression

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

• ###### Re: Challenging Condition/Formula

Yes, I solved it like you. It works!

Thanks a lot for all your help!

• ###### Re: Challenging Condition/Formula

Awesome!!