Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count on multiple variable conditions

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


4 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

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

Not applicable
Author

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


Not applicable
Author

sry forgot to attach a sample