Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sudeep_d
Partner - Creator
Partner - Creator

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

7 Replies
deepakk
Partner - Specialist III
Partner - Specialist III

hi,

Try this code.

if(count(Cust)> 1 ,1)

Deepak

nagaiank
Specialist III
Specialist III

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.

Not applicable

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
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

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
MVP
MVP

Try a set expression like

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

Not applicable

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