First Sale Date

[soton34]

Hi there.

Tried this before but I'm still struggling with it. Must be the way I ask!

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[Prod Code] as LINK_FIRSTDATE_TO_SALESINV,

FirstSale:

Load

[Inv Date] as FIRSTDATE

FROM \\10.1.2.6\blue data\mio47.csv (ansi, txt, delimiter is ',', embedded labels); [LINK_FIRSTDATE_TO_SALESINV],

inner join(FirstSale)

//so, selecting the min date and joining, will be exclude the other dates

MIN( [FIRSTDATE]) as [FIRSTDATE]

resident FirstSale [LINK_FIRSTDATE_TO_SALESINV];

group by

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

[pat_agen]

hi Sam,

it's difficult to see what tables and relationships you have in your qvw file.

But this is how you could get to your result.

You must have a fact table, the table with all your sales. this must have an invoice date a product code and the SALE value.

the granularity of this table is your invoice (or transaction) that is you have one record per transaction.

You need to create a product table with one record per product if you haven't already done so. It is in this table (the product table) that you should put your FirstDate field. The firstDate is an attribute of a product not of an invoice.

The link between the invoice table and your product table will be your product code.

your expression will work because it will only take those products whose FirstDate falls into your range and then will only sum those invoices which fall in the same range.

At the moment your FirstDate field is linked to teh invoices. So when four invoices appear for one product code on teh FirstADte valeu for that product code you are going to count that produuct four times.

hope this is clear.

[soton34]

Dear Pat

Many thanks for your advice.

I've moved the link from the Sales Invoices to the Product code but my results remain the same.

To confirm, I have a table of Sales Invoices which is linked to the Product Code, a table of Product Codes and a table of FirstDates linked to the Product Code (was linked to Sales Invoices)

I've checked the FirstDate table and the Product Code table and there is only one entry against each product, but the results are still being multiplied by the number of invoices on the FirstDate.

Any more thoughts?

Sam

[pat_agen]

hi,

two questions:

what does your final report look like?

is it:

dim1 exp

product sales in date range as per your original expression

if not hwo does your chart/report look?

2nd question:
are the results exactly as before or are they diffrent from before but still wrong? Ie are you still, with the change in the data model, getting the multiplication by the number of transactions a particular product had on its first day? Or is the anomaly coming from something else now?

the answers to these questions will help us move forward.

In a good data model you would not have a FirstDate table rather the FirstDate would be a field in your product table as it essentially is an attribute of a given product.

[soton34]

Dear Pat

Many thanks for your continued assistance and patience.

I am not the most experienced programmer on this forum so please bear with me.....

Question 1 - I'm not sure quite what you are asking but it may help if I explain what I am untimately trying to do. I want to end up with a value for Sales which are new within a given period for all products. This will then allow me to calculate the Like-for-Like sales value increase/decrease. I don't really need it per Product code, just a total figure. It was only when I checked the total figure in a table of product codes that I noticed the problem.

Question 2 - I am confident that the results are the same ( I have checked against dashboard from prior to the reprograming) and yes it still seems to be multiplying by the number of invoices on the FirstDate. I tried a work around by dividing the total value (per product code) by the number of invoices on the FirstDate and this give a sensible total figure but this cannot be used to calculate a total figure as the value changes from £500,000 to £29!

I do understand your comment about a good data model, but I don't know how to join the FirstDate field with the Product table. I have used outer join loads in the past but can I do this with the result of my FirstDate creation script?

Again, thanks for your patience, I am a Sales Administrator trying to learn code to produce figures/reports and my experience so far has been using the help menu and 'pinching' code from other areas of the script and variables to reach a result.

There may be a VERY simple answer to achieve what I need!

Thanks

Sam

[pat_agen]

Hi Sam,

I wont be able to get you an answer today. Hopefully if there is anyone out there following this thread who can help that would be great.

If you could post an example of your qvw with some sample lines - remove any info you don't wish to share across the internet - the I could show you how to contsruct the data model I had in mind.

this looks like a case for our "set analysis" experts, a skill I am still learning!

What you need is to reduce your answer set to those products having been sold for the first time in your time frame - and then with this reduced population sum all the invoces they have again in your time frame.

In theory your expression with if should work but obviously isn't. Being old school I'd look for the answer in the data model but I've seen enough posts on this forum to see that there is more than one way to skin a cat.

[soton34]

Dear Pat

Thanks for your advice and assistance.

I am pleased to advise that following your comments and some random script amendments (don't ask me what I did, but it looked good at the time!) I now have a working variable accurately calculating the NewSales Value within the date range.

Again, I would like to thank you for your persistence and good luck with the Set analysis!

Sam