Calculating the Value of Sales for New Parts in a given date range

[soton34]

Hi there. Never tried this before but I think that I have a cracker for you!

I'm on V8.5

I am trying to calculate the Sales Value for New To Range items in the last 12 months from any given date.

I have two dates Current365From and Current365To which define the reportable area.

I have pinched some script from an earlier post to identify the first date that we sold a particular item and therefore see if it appears in the reportable area:

//__FirstSaleDate

FirstSale:

Load

[Prod Code]

as LINK_FIRSTDATE_TO_SALESINV,

[Inv Date]

as FIRSTDATE

FROM

inner

join(FirstSale) //so, selecting the min date and joining, will be exclude the other dates

Load

[LINK_FIRSTDATE_TO_SALESINV]

,

MIN

( [FIRSTDATE]) as [FIRSTDATE]

resident

group

[LINK_FIRSTDATE_TO_SALESINV];

So I now have a table to tell me the FIRSTDATE of any given Prod Code

My formula to calculate the total value of sales for all new items is as follows:

sum

(IF(FIRSTDATE>=Current365From and FIRSTDATE<=Current365To and INVOICEDATE>=Current365From and INVOICEDATE<=Current365To,SALE))

I use this as a Variable to give me $(Sales365NewParts) as a total sales value for all Prod Codes.

My problem is that when the first sale date has more than one transaction for an individual Prod Code on it then the total is multiplied by the number of transactions on that first date:

I have 2 examples:

Example 1:

INVOICEDATE - Value

31/12/10 - 449.64

31/12/10 - 549.56

31/12/10 - 2997.60

31/12/10 - 999.20

Total Value = 4996 but the formula above gives 19,984

Example 2:

INVOICEDATE - Value

23/06/10 - 40

23/06/10 - 40

25/06/10 - 40

Total Value = 120 but the formula above gives 240

This is not happening with all Prod Codes, just those with more than one INVOICEDATE equal to the FIRSTDATE

Sorry for the long question but I hope the reply is simple!

Any Ideas?

Thanks

Sam

by FirstSale \\10.1.2.6\blue data\mio47.csv (ansi, txt, delimiter is ',', embedded labels);

[#2]

Hi Saton,

I think its not because of your formula . Its because of your joining . Can you use applymap and check the Same .

Regards,

Chakravarthy.

[soton34]

Dear Chakravarty

Many thanks for your reply.

I am not familiar with the applymap function in the script and the help menu advises that it should be used with a "mapping table that has previously been created through the mapping load "

Can you advise how this would work?

Kind regards

Sam