finding common customer in last 2 years

[sudeep_d]

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

[deepakk]

hi,

Try this code.

if(count(Cust)> 1 ,1)

Deepak

[nagaiank]

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.

[#4]

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.

how about...

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

[swuehl]

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.

[sudeep_d]

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

[swuehl]

Try a set expression like

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

[#8]

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