count conditional to sum results

[#1]

Hi,

Im trying to count customers based on a series of sum = to or -= results. I'm using the set expression below but it returns 'error in set modifier'. Any suggestions on what Im doing wrong, or a better way to achieve this.

Thanks in advance

Grant

=If( vFundTypePlayable = '1',
(COUNT(
{$<
[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}
, sum({$<PNL_REAL={'0'}>})
, sum({$<PNL_RELEASED_BONUS={'0'}>})
, sum({$<PNL_PLAYABLE_BONUS-={'0'}>})
>}
DISTINCT [PARTY_ID])
)
)

[Anonymous]

Hi Grant,

Try adding ' = ' before Sum

Hope it will helpful!!

[ramoncova06]

try rather to use intersection with searches in your set analysis

=If( vFundTypePlayable = '1',

(COUNT(

{$

<[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"} >

*

< [PARTY_ID] = {"=sum(PNL_REAL)=0"}>

*

< [PARTY_ID] = {"=sum(PNL_RELEASED_BONUS)=0"}>

*

< [PARTY_ID] = {"=sum(PNL_PLAYABLE_BONUS)<>0"}>

>}

DISTINCT [PARTY_ID])

)

)

[#4]

Thanks Ramon,

using your statement its telling me:

error in expression: '}' expected

It doesn't look like theres any open '{', any suggestions?

[ramoncova06 · Accepted solution]

sorry missed to remove the last >

=If( vFundTypePlayable = '1',

(COUNT(

{$

<[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"} >

*

< [PARTY_ID] = {"=sum(PNL_REAL)=0"}>

*

< [PARTY_ID] = {"=sum(PNL_RELEASED_BONUS)=0"}>

*

< [PARTY_ID] = {"=sum(PNL_PLAYABLE_BONUS)<>0"}>

}

DISTINCT [PARTY_ID])

)

)

[#6]

looks brilliant! thanks Ramon

[#7]

Hi Ramon,

It looks like Im not getting accurate result here. The result Im getting is lower than is I do the same using a sql query to the DB. The query Im using is below, any suggestions here?

SELECT COUNT (*) FROM

  (

    SELECT PARTY_ID

    FROM

      DW_GAME_PLAYER_DAILY

    WHERE

      SUMMARY_DATE >= '2015-01-01'

      AND SUMMARY_DATE < '2015-04-30'

    GROUP BY PARTY_ID

    HAVING

      sum(pnl_real) = 0

      AND SUM(pnl_released_bonus) = 0

      AND Sum(pnl_playable_bonus) <> 0

  ) partyIdTable;

[ramoncova06]

try by adding the date expression into each set

=If( vFundTypePlayable = '1',

(COUNT(

{$

< [PARTY_ID] = {"=sum({<[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}>}PNL_REAL)=0"}>

*

< [PARTY_ID] = {"=sum({<[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}>}PNL_RELEASED_BONUS)=0"}>

*

< [PARTY_ID] = {"=sum({<[SUMMARY_DATE] = { ">=$(vStartDate)<=$(vEndDate)"}>}PNL_PLAYABLE_BONUS)<>0"}>

}

DISTINCT [PARTY_ID])

)

)

[#9]

Thanks Ramon, I'll check that right now

Would Rangecount also be possible with this? I tried to use that instead of count and ran into errors

[ramoncova06]

I don't think so since having each condition separated will give you a different number, but with this you are doing an intersection, the other thing I just noticed and not sure if relevant is that in your query you are not doing a distinct