Distinct count with both And/OR conditions

[#1]

Hi,

I have created the statement below, which successfully counts PARTY_ID that meets all four criteria of date range and types of play count. I'm trying to modify this to perform the distinct count that fits in the date range AND any of the three play_counts rather than  all of them.  I tried changing the operator between the play_counts from , to * however this just gives me an error.

Thanks in advance for any help or suggestions

=(COUNT(distinct
{$<
[COUNT_DATE] = { ">=$(vStartDate)<=$(vEndDate)"},
[REAL_PLAY_COUNT] = {'1'},
[RELEASED_PLAY_COUNT] = {'1'},
[PLAYABLE_PLAY_COUNT] = {'1'}
>}
[PARTY_ID]
)
)

[jolivares]

If I understand you, your require is :

=(COUNT(distinct
{$<
[COUNT_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}>+
<[REAL_PLAY_COUNT] = {'1'}>+
<[RELEASED_PLAY_COUNT] = {'1'}>+
<[PLAYABLE_PLAY_COUNT] = {'1'}
>}
[PARTY_ID]
)
)

[sinanozdemir]

It seems like you have extra parenthesis

=(COUNT(distinct
{$<
[COUNT_DATE] = { ">=$(vStartDate)<=$(vEndDate)"},
[REAL_PLAY_COUNT] = {'1'},
[RELEASED_PLAY_COUNT] = {'1'},
[PLAYABLE_PLAY_COUNT] = {'1'}
>}
[PARTY_ID]
)
)

just get rid of them so like the below:

=(COUNT distinct
{$<
[COUNT_DATE] = { ">=$(vStartDate)<=$(vEndDate)"},
[REAL_PLAY_COUNT] = {'1'},
[RELEASED_PLAY_COUNT] = {'1'},
[PLAYABLE_PLAY_COUNT] = {'1'}
>}
[PARTY_ID]
)

[#4]

Thanks Juan, this works aside for the date range is also an OR. I'm hoping Im explaining this correctly. Let me try another way:

Current:

Date Range AND Real_Play_Count AND Released_Play_Count AND Playable_Play_Count

Desired:

Date Range AND (Real_Play_Count OR Released_Play_Count OR Playable_Play_Count)

[sunny_talwar · Accepted solution]

May be this:

=COUNT(distinct {$<[COUNT_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}, [REAL_PLAY_COUNT] = {'1'}> + <[COUNT_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}, [RELEASED_PLAY_COUNT] = {'1'}>+<[COUNT_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}, [PLAYABLE_PLAY_COUNT] = {'1'}>} [PARTY_ID])

[maxgro]

=COUNT(

       {$

            <COUNT_DATE={">=$(vStartDate)<=$(vEndDate)"},REAL_PLAY_COUNT={1}>             +

            <COUNT_DATE={">=$(vStartDate)<=$(vEndDate)"},RELEASED_PLAY_COUNT={1}>    +

            <COUNT_DATE={">=$(vStartDate)<=$(vEndDate)"},PLAYABLE_PLAY_COUNT={1}>

       }

distinct PARTY_ID)

[#7]

Thanks Maxgro! I marked Sunny's as correct only because he got there first. Both of you were correct and as always I super appreciate everyones help!

Cheers

Grant