topic Re: Count on multiple variable conditions in QlikView

Count on multiple variable conditions

Wed, 24 Jun 2015 15:10:23 GMT

Im trying to count unique party id's using a variety of variable conditions. If I use a single variable condition this works, however when I add more variables it just returns a '-' in the result. I would like to be able to re-use the statement for multiple variable combinations in order to return count unique to those conditions.

=Num(
If( vFundTypeReal = '1' and vFundTypeReleased = '0' and vFundTypePlayable = '0',
(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 on multiple variable conditions

Gysbert_Wassenaar — Wed, 24 Jun 2015 16:22:45 GMT

Do the separate conditions work? Can you post a qlikview document that demonstrates the problem?

Re: Count on multiple variable conditions

Gabriel — Wed, 24 Jun 2015 16:46:10 GMT

Hi,

Take out the quote around you numbers (i.e. '1', '0' should be just 1 and 0)

Re: Count on multiple variable conditions

Thu, 25 Jun 2015 02:41:27 GMT

The separate conditions work, but I just confirmed I get different results than if I do a sql query directly on the DB. My SQL query is below, and the single condition set expression below that. I need to match the results of the SQL query in the set expression, and I need to be able to use multiple conditions such as you see in the text box for the sample where I have the possibility of any combination of true/false using vFundTypeReal = '1' AND vFundTypeReleased = '0' AND vFundTypePlayable = '0'

SQL Query

SELECT COUNT (*) FROM

(

SELECT PARTY_ID

FROM

DW_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;

Set Expression:

=If( vFundTypeReal = '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 on multiple variable conditions

Thu, 25 Jun 2015 02:52:52 GMT

sry forgot to attach a sample