Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
)
))
Do the separate conditions work? Can you post a qlikview document that demonstrates the problem?
Hi,
Take out the quote around you numbers (i.e. '1', '0' should be just 1 and 0)
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])
)
)
sry forgot to attach a sample