6 Replies Latest reply: Jan 21, 2011 8:51 PM by michaelm

# Set analysis - advanced searches

Hi,

I'm trying to achieve the following: I want to get all the clients who have bought something in Q1/2010 and nothing in Q1/2009. I tried with following set analysis but it's not working correctly.

=COUNT({1<_ClientID = {"=SUM({<QuarterYear = {'Q1/\$(vCurrentYear)'}>} _Sales) > 0"}, _ClientID = {"=SUM({<QuarterYear= {'Q1/\$(vPreviousYear)'}>} _Sales) = 0"} >} DISTINCT _ClientID)

Can you help me? Thanks in advance!

PS : Is there anywhere more detailed information about Advanced searches in set analysis?

• ###### Set analysis - advanced searches

Hi Skoezzi,

try this:

`=COUNT( {1 < "SUM( {<QuarterYear = {'Q1/\$(vCurrentYear)'>} _Sales) > 0", "SUM( {<QuarterYear= {'Q1/\$(vPreviousYear)'}>} _Sales) = 0" >} DISTINCT _ClientID)`

Even it may be not the very correct solution it should point you into the right direction.

Regards Roland

• ###### Set analysis - advanced searches

I really can't get it to work. It seems that qlikview takes only the first condition and not the second. I need to combine a lot conditions like this. I'm already searching days for this...

• ###### Set analysis - advanced searches

Are you able to send an exam application? With a few data and/or scrampled data. It doesn't matter If you are using a personal edition.

• ###### Set analysis - advanced searches

Did you ever figure this out ? I'm trying to do the exact same. Thanks

• ###### Set analysis - advanced searches

Something like this:

`count(distinct aggr(if (sum({1 <dmYear={\$(=vCurrYear - 1)}, dmQuarter={\$(='Q' & num(vCurrQuarter))}>}Sales) > 0 and sum({1 <dmYear={\$(=vCurrYear)}, dmQuarter={\$(='Q' & num(vCurrQuarter))}>}Sales) = 0, ClientId) , ClientId ))`

See example in attachment.

• ###### Set analysis - advanced searches

http://www.qlik.com/us/explore/resources?language=english&page=6

There is a video tutorial on Set Analysis that contains the p and e options with set analysis. It is very helpful and should show you how to answer your question using the p and e options of set analysis