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

[RheaQv12]

Hey Sunny, did i confuse you?

[sunny_talwar · Accepted solution]

Try this

=Count(DISTINCT {<FirstNameLastName = {"=Count(DISTINCT {<Attestation = {'Not in Scope'}>} Attestation) = 1 and Count(DISTINCT {<Attestation = {'In Scope'}>} Attestation) = 1 and Concat(DISTINCT {<Attestation = {'Not in Scope'}>} Attestation, ',') <> Concat(DISTINCT {<Attestation = {'In Scope'}>} Attestation, ',')"}>} Email)

[RheaQv12]

Hey Sunny,

thanks a lot..you are truly genius!

[RheaQv12]

Hey Sunny,

what it does is - count the persons emails only when there is at least 1 in scope attestation and 1 out of scope attestation and the attestation should not be same then only count the emails - right? thanks a lot

[sunny_talwar]

Mostly yes except the last part where it checks if for a attestation they have different emails

Emails for not in scope

Concat(DISTINCT {<Attestation = {'Not in Scope'}>} Attestation, ',')

<>

Emails for in scope

Concat(DISTINCT {<Attestation = {'In Scope'}>} Attestation, ',')