7 Replies Latest reply: Jul 13, 2012 8:27 AM by klangley

# finding common customer in last 2 years

Hi,

i have table   year   cust

2012   aa

2012   bb

2012   cc

2011    aa

2011    dd

here now since 'aa' repeats for both year 2011 and 2012, so i want to retrieve aa, what should be the expression??

thanks

• ###### Re: finding common customer in last 2 years

hi,

Try this code.

if(count(Cust)> 1 ,1)

Deepak

• ###### Re: finding common customer in last 2 years

You are not accounting for the multiple year requirement.  I'd imagine this is a portion of a sales table and we are looking at a customer key.

aggr ( count( \${<year = {2011,2012} >} Distinct year ) , cust ) = 2

• ###### Re: finding common customer in last 2 years

Maybe like

=concat({<cust = p({<year={2011}>})*p({<year={2012}>})>} distinct cust,', ')

e.g.  in a text box, or

=sum({<cust = p({<year={2011}>})*p({<year={2012}>})>} sales)

as expression in a chart with dimension cust (in fact, the dimension doesn't matter, the set expression

{<cust = p({<year={2011}>})*p({<year={2012}>})>}

should limit any aggregation functions to records for the customers belonging to both years only.

• ###### Re: finding common customer in last 2 years

hi swuehl,

this some wad solves my issue, what if i want to know the common customer in last two year who shares common product say i20.

rest of all thanks for ur concern.

thanks

• ###### Re: finding common customer in last 2 years

Try a set expression like

{<cust = p({<year={2011}, product={i20}>})*p({<year={2012}, product={i20}>})>}

• ###### Re: finding common customer in last 2 years

For product I20.  Use any of the above formulas and use a selector for product.

• ###### Re: finding common customer in last 2 years

You can use And-Mode listbox also for this case. The details about the And-Mode listboxes are in the QlikView Reference Manual. I have attached a qvw file using And-Mode listbox.

Hope this helps.