how to write expression

[kunkumnaveen]

Hello,

        how to get count of common customers who bought product in both Finance and AD in the same year

i wrote a expression for common customers who bought product in  Finance and AD irrespective of year,

i mean if some customer bought a finance product in 2014 and same customer bought a AD in 2016 that will make my count as 1

which i am able  to achieve by below expression

Count(distinct{<MGCID =p({<Group ={'AD'}>})* p({<Group = {'Finance'}>})>}MGCID)    (working good)

Now i want count of customers who bought in same year

i mean if some customer bought a finance product in 2014 and same customer has to buy a AD product  in 2014 then my count is 1

i have Year field

[vishsaggi]

May be try this?

= Count(distinct{<MGCID =p({<Group ={'AD'},Year={" >= Year(Date(Min(Date_Id))) "}>})* p({<Group = {'Finance'},Year={"$(=max(Year))>})>}MGCID)

[sunny_talwar · Accepted solution]

May be create a new field in the script using

LOAD ...

    AutoNumber(MGCID & Year) as Key

and then try this

Count(DISTINCT {<Key = p({<Group ={'AD'}>}) * p({<Group = {'Finance'}>})>} MGCID)

or something like this

=Sum(Aggr(If(Count({<Group = {'AD'}>} MGCID) > 0 and Count({<Group = {'Finance'}>} MGCID) > 0, 1, 0), MGCID, Year))

[kunkumnaveen]

Thanks Sunny