Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use Year as a dimension on the object you want to show.
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
Did you try my proposal from this thread?
Sample attached
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
Not sure I understand anything from your image? What isn't working?