topic Re: Distinct count with both And/OR conditions in QlikView

Distinct count with both And/OR conditions

Wed, 18 Nov 2015 21:07:50 GMT

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

Re: Distinct count with both And/OR conditions

jolivares — Wed, 18 Nov 2015 21:45:18 GMT

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

Re: Distinct count with both And/OR conditions

sinanozdemir — Wed, 18 Nov 2015 21:47:12 GMT

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

Re: Distinct count with both And/OR conditions

Wed, 18 Nov 2015 22:02:50 GMT

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)

Re: Distinct count with both And/OR conditions

sunny_talwar — Wed, 18 Nov 2015 22:10:56 GMT

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

Re: Distinct count with both And/OR conditions

maxgro — Wed, 18 Nov 2015 22:20:21 GMT

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

Re: Distinct count with both And/OR conditions

Thu, 19 Nov 2015 04:41:02 GMT

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