New Buyers to a Branch

[Anonymous]

Hi all I am wanting to count the number of new buyers to a specific branch.

New buyers would be a buyer who has bought a unit in June but not between Dec - May at that same branch.

=aggr(if(

count(

{< SOLD.YrMthSeq= {"<=$(vRptYrMthSeqTY ) >=$(vRptYrMthSeqTY) "},

UNIT.STOCKSTATUS={'Sold'} >}

UNIT.TOTALSOLD) > 0

and

count(

{<SOLD.YrMthSeq= {"<=$(=$(vRptYrMthSeqTY) -1) >=$(=$(vRptYrMthSeqTY) -6)"},

UNIT.STOCKSTATUS={'Sold'} >}

UNIT.TOTALSOLD) = 0 ,

count( BUYER.B_NO)) , BUYER.B_NO)

My dimension is branch and the first set analysis is asking if they have bought something in june the second is if they have bought something between Dec - May.

Any help would be very appreciated.

Thanks

Gareth

[Anonymous]

I think this is really close Jonathan however the expression doesn't like the DISTINCT part if I take it out it works but it wont be giving me an accurate number.

[jonathandienst · Accepted solution]

My bad - the Distinct should be before the inner Aggr():

=Sum(Aggr(

  Count(DISTINCT Aggr(

  If(Count({<SOLD.YrMthSeq={"<=$(vRptYrMthSeqTY) >=$(vRptYrMthSeqTY)"}, UNIT.STOCKSTATUS={'Sold'}>} UNIT.TOTALSOLD) > 0

  And Count({<SOLD.YrMthSeq={"<=$(=$(vRptYrMthSeqTY)-1)>=$(=$(vRptYrMthSeqTY)-6)"}, UNIT.STOCKSTATUS={'Sold'} >} UNIT.TOTALSOLD) = 0,

  BUYER.B_NO)

  , BUYER.B_NO, Branch))

,Branch))

[Anonymous]

Thank you so much Jonathan! That worked!!

Thanks

Gareth