12 Replies Latest reply: May 23, 2017 5:18 AM by naveen kumar

# how to write expression

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

• ###### Re: how to write expression

try this

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

• ###### Re: how to write expression

i want to display all the three year not only a max year.......

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

• ###### Re: how to write expression

instead of max function i tried Only function  but its not working

• ###### Re: how to write expression

if you select the particular year, don't you think, below expression should work?

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

• ###### Re: how to write expression

Any suggestions plz

• ###### Re: how to write expression

Not sure may be this?

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

• ###### Re: how to write expression

Bonjour,

Je pense que tu peux utilises la fonction AGGR.

Tu devrais plutôt l'écrire comme ceci:

Count( Distinct Total <MGCID > Aggr(tonExpression, Dimensions, autresDimensions)

Par "tonExpression", je pense à ta grande expression qui calcule le retard (je crois)

Comme dimensions, il faut mettre MGCID , plus les autres dimensions (séparées par des virgules ) qui permettent d'identifier une valeur unique pour les champs utilisé dans ton expression : ...

• ###### Re: how to write expression

How are you filtering by year? Do you have a master calendar or a time dimension table? Or are you just using the date from your data set to filter on?

• ###### Re: how to write expression

i have master calender  and if i have any filtering by year i use

{<Calendrier.year={\$(=Year(Max(Date_id)))}>}

• ###### Re: how to write expression

May be try this?

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

• ###### Re: how to write expression

May be create a new field in the script using

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

• ###### Re: how to write expression

Thanks Sunny