topic Set analysis - advanced searches in QlikView

Set analysis - advanced searches

Fri, 26 Nov 2010 10:35:40 GMT

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

Fri, 26 Nov 2010 11:36:49 GMT

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

Fri, 26 Nov 2010 14:00:08 GMT

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

Fri, 26 Nov 2010 14:04:43 GMT

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

Thu, 20 Jan 2011 18:11:23 GMT

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

Set analysis - advanced searches

Thu, 20 Jan 2011 21:08:09 GMT

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

Sat, 22 Jan 2011 01:51:59 GMT

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