Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sudeep_d
Contributor

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
Valued Contributor III

Re: finding common customer in last 2 years

hi,

Try this code.

if(count(Cust)> 1 ,1)

Deepak

nagaiank
Valued Contributor III

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.

Not applicable

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.

how about...

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

MVP
MVP

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.

sudeep_d
Contributor

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

MVP
MVP

Re: finding common customer in last 2 years

Try a set expression like

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

Not applicable

Re: finding common customer in last 2 years

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

Community Browser