how to add year in the expression

[kunkumnaveen]

Hello,

I Want to count  only those customer ID 's who have bought FE product and AD product both in same year

table1:

ID     year            product  

101           2008             FE

102           2008             FE

103           2009             FE

104           2014              FE        

table2:

ID     year            product  

101           2008           AD

102           2010            AD

103           2013            AD

104            2014             AD

i mean to say from table1

101           2008             FE

104           2014              FE 

and table2

101           2008           AD

104            2014             AD

my count is 2

expression i wrote :

=Count(DISTINCT{<ID = p({<product= {'FE'}>})*p({<product ={'AD'}>})>}ID)  this expression is only to get count of common ID's from both table irrespective of the year they bought  (working great)

but how to add year in it

can any help plz

thanks

naveen

[sibusiso90]

Use Year as a dimension on the object you want to show.

[kunkumnaveen]

Hello

          i have already got  year and product as dimension

expression =Count(DISTINCT{<ID = p({<product= {'FE'}>})*p({<product ={'AD'}>})>}ID)

i think we should bring that year in to that expression then only it works

[sunny_talwar]

Did you try my proposal from this thread?

Re: how to write expression

[sunny_talwar]

Sample attached

[kunkumnaveen]

hello

i tried this from your previous thread

=Sum(Aggr(If(Count({<PRODUCT = {'AD'}>} MGCID) > 0 and Count({<PRODUCT = {'CMH'}>} MGCID) > 0, 1, 0), MGCID, year))

but its not working

i am using personal edition

[sunny_talwar]

Not sure I understand anything from your image? What isn't working?