Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aviadbein
New Contributor II

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

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

1 Solution

Accepted Solutions
MVP
MVP

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)


Capture.PNG

13 Replies
MVP
MVP

Re: Challenging Condition/Formula

Try this as your second dimension

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

aviadbein
New Contributor II

Re: Challenging Condition/Formula

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

MVP
MVP

Re: Challenging Condition/Formula

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

MVP
MVP

Re: Challenging Condition/Formula

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

MVP
MVP

Re: Challenging Condition/Formula

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

Highlighted
aviadbein
New Contributor II

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)

MVP
MVP

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)


Capture.PNG

aviadbein
New Contributor II

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

MVP
MVP

Re: Challenging Condition/Formula

I am seeing a count of 2 for good?

Capture.PNG