Number os sales accumulated

[ecabanas]

Hi guys again,

I want to know the number of the sales of a table.

I have this code to know the frequency, but I also want to know wich sale number is, For example "The third o the second"

Result:

LOAD

     SALESID,

     CUSTACCOUNT,

     CREATEDDATETIME,

If(CUSTACCOUNT=Previous(CUSTACCOUNT), CREATEDDATETIME-Previous(CREATEDDATETIME)) as "Frequency",

Count(DISTINCT SALESID) AS "nUMBER"

Resident SALESTABLEPRE

Order By SALESID, CUSTACCOUNT, CREATEDDATETIME;

But did not work 😞

Many thanks

Eduard

[swuehl]

A Count() in your LOAD statement requires a GROUP BY clause.

I am unsure what you want to achieve, could you upload a few lines of sample data and your requested result?

[ecabanas]

Hi,

My table has SalesId, Customer and date.

I wanna know is, the number of days from one order to another and what order number is (the second, the third, etc)

I wanna answer this question: How many days take our customers from 2nd to 3rd order?

SalesId	Customer	Date	Frequency	#SALES
PV0001	C0001	01/01/2015		1
PV0002	C0002	01/01/2015		1
PV0003	C0003	01/01/2015		1
PV0004	C0001	01/02/2015	31	2
PV0005	C0002	10/02/2015	40	2
PV0006	C0003	15/02/2015	45	2
PV0007	C0001	01/03/2015	28	3
PV0008	C0002	14/03/2015	32	3
PV0009	C0003	18/03/2015	31	3

[swuehl · Accepted solution]

I would sort the input table first by Customer, then by SalesId or Date. You can use AutoNumber to count the Sales order per Customer:

Result:

LOAD

     SALESID,

     CUSTACCOUNT,

     CREATEDDATETIME,

If(CUSTACCOUNT=Previous(CUSTACCOUNT), CREATEDDATETIME-Previous(CREATEDDATETIME)) as "Frequency",

Autonumber(SALESID, CUSTACCOUNT)  AS [#SALES]

Resident SALESTABLEPRE

Order By CUSTACCOUNT, SALESID;

[Digvijay_Singh]

This also works but I think Stefan's one is better one -

temp:

Load * Inline [

SalesId Customer Date

PV0001 C0001 01/01/2015

PV0002 C0002 01/01/2015

PV0003 C0003 01/01/2015

PV0004 C0001 01/02/2015

PV0005 C0002 10/02/2015

PV0006 C0003 15/02/2015

PV0007 C0001 01/03/2015

PV0008 C0002 14/03/2015

PV0009 C0003 18/03/2015

] (delimiter is '');

Output:

Load *,if(Customer=previous(Customer),Date(Date#(Date,'DD/MM/YYYY'))-Date(Date#(Peek('Date',-1),'DD/MM/YYYY'))) as Frequency,

  if(Customer<>previous(Customer),1,alt(peek('SaleNBR',-1),0)+1) as SaleNBR

Resident temp Order By Customer,SalesId;

Drop table temp;

[ecabanas]

WOWWWW again!!! many many thank's

if you come to Barcelona a paella is waiting you 😉

Regards

Eduard

[ecabanas]

Hi Swuehl

Checking the result the Frequency calculation is not correct, did you know why?

Many thank's

SALESID	#SALES	DAYFREQ	CREATEDDATETIME	CUSTACCOUNT
PR10243479	PR10243479	PR10243479	02/11/2014	C111507
PV90004533	PV90004533	PV90004533	04/11/2014	C111507
PV00225593	1		31/05/2014	C111507
PV00233252	2	10498	04/08/2014	C111507
PV00236167	3	6435	29/08/2014	C111507
PV00239092	4	450	22/09/2014	C111507
PV00241538	5	-627	11/10/2014	C111507
PV00243479	6	1379	29/10/2014	C111507
PV00245565	7	5152	17/11/2014	C111507
PV00249565	8	1291	20/12/2014	C111507
PV00251357	9	1713	10/01/2015	C111507
PV00254782	10	6858	07/02/2015	C111507
PV00259194	11	1159	15/03/2015	C111507
PV00278699	12	17732	19/08/2015	C111507

[swuehl]

Could you post your script code you have used to create the ouput?