need help with the expression

[RheaQv12]

Guys,

I have a daily .csv file which i load into QVD  - its like users of an application and i am trying to derive or calculate the following expression...but its not giving me the right result

AGGR(count( if(Scope='In Contract' OR Scope='Not in Contract', Email),Date,Name)

I need to find the persons with 2 different emails for the 'In Contract' and 'Not in Contract' scope for each day for each person ... for the person or user when the scope is 'In Contract' and 'No In Contract' then only look for that persons emails and if they are different then count for that day.... attached sample file

thanks a lot

[sunny_talwar]

Based on the sample attached, what is the count you expect to see and in which object?

[RheaQv12]

Hey Sunny, thank you. I think my expression wit the if condition is wrong.. the expression should only look for the users when they have 'In Contract'

i am looking for the straight table -

[RheaQv12]

Sunny, thanks a lot

[RheaQv12]

Sunny,

Please ignore the message with "he expression should only look for the users when they have 'In Contract'  "

This should been "The expression should only look for the users when they have "In Contract" and "Not In Contract"

[sunny_talwar]

Why did you not include Michael Mose? He seems to have two different ids?

=Count(DISTINCT {<Name = {"=Count(DISTINCT {<Scope = {'In Contract', 'Not in Contract'}>} Email) > 1"}>} Email)

[RheaQv12]

Hey Sunny,

Yes you are right Mike should have been included. This is the sample dataset that i prepared to get my actual dataset working - so i used the same expression on my real dataset - I only need to count the users email when there is an email associated with 'in cotract' and there is an another email associated with 'not in contract' 

this expression is counting all when there is "in contract" or "not in contract"  say for example for 11/13

11/13  ABC,X  abc@yahoo.com   In Contract

11/13  ABC,X abc@gmail.com     Not in Contract

since this user has 2 emails - 1 associated with in contract and 1 associated with not in contract i count this as 2

11/13  XYX,Z   xyx@yahoo.com  In Contract

11/13 XYX,Z  xyx@yahoo.com   In Contract

I dont count this user at all as his email is not associated with both "in contract" and "not in contract"

attached the dataset that i was using

again, thanks a lot for helping with this expression

[RheaQv12]

11/13  YYY,Z   yyy@yahoo.com  In Contract

11/13 YYY,Z  yyy@gmail.com   In Contract

another example where the user has 2 diff emails but the scope is same - i dont need to count this record

i need to count only when the user has 2 diff emails with 2 diff scopes like below

11/13 David,David   david@gmail.com In Contract

11/13 David,David david@yahoo.com Not in Contract

i count this user with 2 as no of emails

please let me know if this is not clear

[sunny_talwar]

What is the issue with the expression I gave? Is that not working? Can you share a scenario where it isn't doing what you need it to do?

[RheaQv12]

Hey Sunny, thanks a lot

when we say scope={'In Contract','Not In Contract'} = This will take into users scope when it is "in contract" or "not in contract" - right? so it is calculating the distinct email ids when the scope is either one of this - right?

i only need to consider if the user has scope as "in contract' and there is an email id and also there is scope "not in contract" and there is also another email with it - in the below example the attestation in each row is "in contract" or (in scope ) i just used in contract for the sample data

my expression is - attached some sample data - again, really appreciate your time

=COUNT(Distinct {<FirstNameLastName={"=Count(Distinct {<Attestation={'In Scope','Not in Scope'}>} Email) >1" }>} Email)