topic Re: count conditional to sum results in QlikView

count conditional to sum results

Tue, 23 Jun 2015 16:25:36 GMT

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

Re: count conditional to sum results

Anonymous — Tue, 23 Jun 2015 16:31:37 GMT

Hi Grant,

Try adding ' = ' before Sum

Hope it will helpful!!

Re: count conditional to sum results

ramoncova06 — Tue, 23 Jun 2015 16:50:20 GMT

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

)

Re: count conditional to sum results

Tue, 23 Jun 2015 22:16:12 GMT

Thanks Ramon,

using your statement its telling me:

error in expression: '}' expected

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

Re: count conditional to sum results

ramoncova06 — Tue, 23 Jun 2015 22:19:18 GMT

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

)

Re: count conditional to sum results

Tue, 23 Jun 2015 23:47:55 GMT

looks brilliant! thanks Ramon

Re: count conditional to sum results

Thu, 25 Jun 2015 14:58:24 GMT

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;

Re: count conditional to sum results

ramoncova06 — Thu, 25 Jun 2015 15:07:05 GMT

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

)

Re: count conditional to sum results

Thu, 25 Jun 2015 15:25:31 GMT

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

Re: count conditional to sum results

ramoncova06 — Thu, 25 Jun 2015 15:32:27 GMT

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

Re: count conditional to sum results

Thu, 25 Jun 2015 16:09:13 GMT

because its grouped by party id it becomes unique so Distinct isn't required, I was able to confirm this in the sub query results.

Your suggestion above returns a "error in set modifier ad hoc element list. ',' or ')' expected", with the following underlined.

"}>}PNL_REAL)=0"}>

Re: count conditional to sum results

ramoncova06 — Thu, 25 Jun 2015 16:26:59 GMT

I don't see where the error could be since all of the brackets are closed properly