Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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...
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.
Did you ever figure this out ? I'm trying to do the exact same. Thanks
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.
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