New Buyers

[Anonymous]

Hi all,

I'm trying to show in a table the number of new buyers we have to each branch. New Buyers are buyers that have never bought before.

count(

		{<
		UNIT.STOCKSTATUS = {'Sold'},
		BUYER.B_NO =
		p({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-0)  >=$(=$(vRptYrMthSeqTY)-0)"}>}) *
		e({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-4)  >=$(=$(vRptYrMthSeqTY)-11)"}>}) *
		e({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-1)  >=$(=$(vRptYrMthSeqTY)-3)"}>}),
		BUYER.B_CATEGORY = {'*'}-{'PR'}

		>}
	DISTINCT BUYER.B_NO)

Could someone check to see if my set analysis is correct please?

Buyer B_No is the unique number for each buyers.

Thanks

[tamilarasu]

Try like below one,

count({<

UNIT.STOCKSTATUS = {'Sold'},

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

BUYER.B_CATEGORY = {'*'}-{'PR'}>}

DISTINCT BUYER.B_NO)

What is the value of the variable vRptYrMthSeqTY.? Also can you explain below statement!!

p({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-0)  >=$(=$(vRptYrMthSeqTY)-0)"}>}) *

e({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-4)  >=$(=$(vRptYrMthSeqTY)-11)"}>}) *

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

It would be helpful to guide you.

[Anonymous]

Thanks for your response Tamil,

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

The above is a calendar we use -0 is September so -1 would be August -2 July and so on.

p({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-0)  >=$(=$(vRptYrMthSeqTY)-0)"}>}) *

e({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-4)  >=$(=$(vRptYrMthSeqTY)-11)"}>}) *

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

With the above set analysis I am trying to say the buyer who bought in September but exclude the ones that bought 11 months before September.

I could write this as e({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-1)  >=$(=$(vRptYrMthSeqTY)-11)"}>})

I am very new to e's and p's by the way so I could be completely wrong!!

[tamilarasu]

Hi Gareth,

Ok. I just want to know the value of variable $(vRptYrMthSeqTY).? I guess we don't need P() and E(). Simply we can mention the month numbers.

[gardenierbi]

For a better performance, solve this in your script! See New/Lost/Returning/Loyal Customers

[somenathroy]

Hi,

You may try the below expression:

count(

     {<UNIT.STOCKSTATUS = {'Sold'},

     BUYER.B_NO ={"sum({<SOLD.YrMthSeq ={'<$(vRptYrMthSeqTY)'},UNIT.STOCKSTATUS = {'Sold'},

     BUYER.B_CATEGORY = {'*'}-{'PR'} , SOLD.YrMthSeq>} Sales_Amount)<=0"}

     BUYER.B_CATEGORY = {'*'}-{'PR'}  >}

DISTINCT BUYER.B_NO)

Idea is to find out the buyers having zero sales before September (or selected month) along with other sets. Considered that Sales_Amount is the field containing brought value by Buyer.

Regards,

Som

[Anonymous]

Hi Tamil,

The value is a number so $(vRptYrMthSeqTY). would equal 57 its the number of the month we are in.

Are you thinking about something like this,

count({<UNIT.STOCKSTATUS = {'Sold'},  BUYER.B_CATEGORY -= {'PR'},

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

-

<UNIT.STOCKSTATUS = {'Sold'},  BUYER.B_CATEGORY -= {'PR'},

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

>} 

DISTINCT  BUYER.B_NO)

[tamilarasu]

Hi Gareth,

Sorry for the late reply. Yes, i thought the same. Any issues in the above set analysis which you mentioned.

Edit: Did you alter your first expression like this and try.

count(

		{<
		UNIT.STOCKSTATUS = {'Sold'},
		BUYER.B_NO =
		p({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-0)  >=$(=$(vRptYrMthSeqTY)-0)"}>}BUYER.B_NO) *
		e({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-4)  >=$(=$(vRptYrMthSeqTY)-11)"}>}BUYER.B_NO) *
		e({<SOLD.YrMthSeq =  {"<=$(=$(vRptYrMthSeqTY)-1)  >=$(=$(vRptYrMthSeqTY)-3)"}>}BUYER.B_NO),
		BUYER.B_CATEGORY = {'*'}-{'PR'} >} DISTINCT BUYER.B_NO)

[Anonymous · Accepted solution]

Hi all,

Thanks for everyone's responses I have managed to solve this now.

count(

		{<
		UNIT.STOCKSTATUS = {'Sold'},BUYER.B_CATEGORY = {'*'}-{'PR'},VENDOR.C_GROUP = {'TCG'},SOLD.YrMthSeq = {"<=$(=$(vRptYrMthSeqTY)-0) >=$(=$(vRptYrMthSeqTY)-0)"},
		BUYER.B_NO =
		e({<SOLD.YrMthSeq = {"<=$(=$(vRptYrMthSeqTY)-1) >=$(=$(vRptYrMthSeqTY)-11)"},BUYER.B_CATEGORY = {'*'}-{'PR'},UNIT.STOCKSTATUS = {'Sold'},VENDOR.C_GROUP = {'TCG'}>})

		>}
	DISTINCT  BUYER.B_NO)

Thanks

[tamilarasu]

Hi Gareth,

Glad that you solved it. .