topic Re: New Buyers in QlikView

New Buyers

Anonymous — Fri, 16 Oct 2015 07:58:29 GMT

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

Re: New Buyers

tamilarasu — Fri, 16 Oct 2015 08:26:19 GMT

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.

Re: New Buyers

Anonymous — Fri, 16 Oct 2015 08:33:45 GMT

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

Re: New Buyers

tamilarasu — Fri, 16 Oct 2015 08:49:55 GMT

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.

Re: New Buyers

gardenierbi — Fri, 16 Oct 2015 08:52:33 GMT

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

Re: New Buyers

somenathroy — Fri, 16 Oct 2015 11:32:07 GMT

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

Re: New Buyers

Anonymous — Fri, 16 Oct 2015 14:57:05 GMT

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)

Re: New Buyers

tamilarasu — Mon, 19 Oct 2015 04:29:34 GMT

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)

Re: New Buyers

Anonymous — Mon, 19 Oct 2015 10:05:16 GMT

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

Re: New Buyers

tamilarasu — Mon, 19 Oct 2015 10:17:39 GMT

Hi Gareth,

Glad that you solved it. .