topic Re: New Customers Competition - chart in QlikView

New Customers Competition - chart

Wed, 25 Sep 2013 10:26:28 GMT

Hi all!

I'm quite new with QV, and I'm having a problem with my expression, where I should have only customers who are new or haven't bought anything in last 18 months before the competition (and who have bought over 300€). Competition started 01.08.2013.

I am doing something wrong with that past 18 months..

This is the expression I have used:

if(sum({$<Invoicedate={">='01.02.2012'"},Invoicedate={"<'31.07.2013'"}>}Invoiced)=0 and (sum(Invoiced)>= '300'),sum(Invoiced),0)

but it still gets all the customers. Could somebody help me with my expression, please?

-Kirsi-

Re: New Customers Competition - chart

swuehl — Wed, 25 Sep 2013 11:14:53 GMT

Are you using a chart with Customer as dimension?

Then this chart expression might work, but you should use Invoicedate as field in your set modifier only once:

=if(sum({$<Invoicedate={">=01.02.2012<31.07.2013"}>} Invoiced)=0 and (sum(Invoiced)>= 300),sum(Invoiced),0)

or if you need to check for Invoiced > 300 after Aug 1st:

=if(sum({$<Invoicedate={">=01.02.2012<31.07.2013"}>} Invoiced)=0 and (sum({$<Invoicedate={">31.07.2013"}>}
Invoiced)>= 300),sum(Invoiced),0)

To only show the customers that fulfill this condition, this should be your only expression and 'Suppress zero values' should be checked on presentation tab.

Re: New Customers Competition - chart

Wed, 25 Sep 2013 12:43:05 GMT

Thank you!

Works perfectly now!

-Kirsi-