Remove Credit Notes

[Anonymous]

Hi Everyone,

Just wondered if anyone can help with this one.

I need to be able to deduct credit notes from the overall invoices.

I have a table of invoices which may or may not have credit notes against them, see the credit note table.

I firstly need to take away the credit amounts from each of the columns relating to identical reference numbers. i.e deduct the credit notes from the invoice amounts.

I then need my table to bring back a count of only 1 overall to enable me to work out an average.

It's a little difficult to explain but effectively if I have 3 invoices and 1 credit note with the same reference I want the report to remove the credit values from the columns then to count only 1 as all the identical reference numbers relate to only one cost.

This will then enable me to work out the average costs.

Many Thanks

Kind Regards

Wendy

[Anonymous]

Hi John,

OK Thanks your a star.

Hope you enjoy the celebrations.

Kind Regards

Wendy

[Anonymous]

Sorry I meant to say Andrew

Just come off the phone from speaking to John

[effinty2112]

Hi Wendy,

In the tables you gave yesterday what is the expression you use?

If I understand you correctly you want to sum up the value of invoices (or is it the count of invoices?) without doubling the value up if the invoice ref crops up twice (or more).

If the ref appears in more than one month then to what month do you want the invoice value to be booked against?

Is the data set you posted earlier good representation of the one you're working on?

With the data already supplied check this table out for ref 10136.

Date	DocType	I Costs	ID	L Amount	Other	Other1	Pat Amount	Pit Amount	Ref	Supplier
24/03/2016	Invoice	0		635.35	0		1554.44	371.82	10136	N
29/04/2016	Invoice	0		0	0		148.55	0	10136	N
11/05/2016	Invoice	0		0	404.55		0	0	10136	N
25/05/2016	Invoice	0		0	404.55		0	0	10136	N
25/05/2016	Invoice	0		635.35	0		1554.44	371.82	10136	N
	Credit	0	1001943	0	404.55	0	0	0	10136
	Credit	0	1001950	635.35	0	0	1554.44	371.82	10136

How would you like these figure to be aggregated and allocated to a month?.

We'll get there I'm sure Wendy.

Kind regards

Andrew

[Anonymous]

Hi Andrew,

Hope you had a good celebration.

Thanks for coming back to me.  The expression I used was Count (Distinct Ref)

I want to count the number of Unique References regardless of the Month.  If it appears in more than one month I would like it to appear in the 1st month that it shows. 

I am trying to get an average cost by reference so if it appears more than once it will dilute the average if that makes sense.

In the table you have given as an example I would only want 1 count for Reference 10136 regardless if it is an Invoice or a Credit Note and would like it to appear in the March Month as this is the 1st month it appears.

I will then be able to divide the overall costs by the count of the references to obtain my result.

I hope this makes sense and thanks once again for your input on this.

Kind Regards

Wendy

[effinty2112]

Hi Wendy,

Hope this gets us closer to the solution. Add this piece of script to the end of your load script.

RefByEarliestMonth:

LOAD

Ref,

Month(Min(Date)) as EarliestMonth

Resident [All Docs] Group by Ref;

Looking at ref 10136 these are the associated records as in previous post less a couple of columns:

Date	DocType	L Amount	Other	Other1	Pat Amount	Pit Amount	Ref	Supplier
24/03/2016	Invoice	635.35	0		1554.44	371.82	10136	N
29/04/2016	Invoice	0	0		148.55	0	10136	N
11/05/2016	Invoice	0	404.55		0	0	10136	N
25/05/2016	Invoice	0	404.55		0	0	10136	N
25/05/2016	Invoice	635.35	0		1554.44	371.82	10136	N
	Credit	0	404.55	0	0	0	10136
	Credit	635.35	0	0	1554.44	371.82	10136

This straight table, there is one dimension Month(Date), is what you don't want:

=Month(Date)	Count(DISTINCT Ref)	Sum([L Amount])	Sum(Other)	Sum(Other1)	Sum([Pat Amount])	Sum([Pit Amount])
		1906.05	1213.65	0	4811.87	1115.46
Mar	1	635.35	0	0	1554.44	371.82
Apr	1	0	0	0	148.55	0
May	1	635.35	809.1	0	1554.44	371.82
	1	635.35	404.55	0	1554.44	371.82

If you take out the calculated dimension Month(Date) and replace it with the field we made in our script the table becomes this, which looks promising as the figures tally with the totals returned above:

EarliestMonth	Count(DISTINCT Ref)	Sum([L Amount])	Sum(Other)	Sum(Other1)	Sum([Pat Amount])	Sum([Pit Amount])
Mar	1	1906.05	1213.65	0	4811.87	1115.46

If we clear the ref field we get this for the records in the data you posted:

EarliestMonth	Count(DISTINCT Ref)	Sum([L Amount])	Sum(Other)	Sum(Other1)	Sum([Pat Amount])	Sum([Pit Amount])
Jan	53	33772.11	3588.67	583	34790.95	14511.29
Feb	28	26825.35	1939.32	315.2	31894.2	12473.21
Mar	28	19411.63	4069.9	269.25	32809.14	8505.25
Apr	27	16925.8	1707.67	474	26903.13	7944.37
May	22	28072.01	263.23	0	12011.59	8985.63

These numbers look very different from the ones you posted, I'm hoping that's down to your actual data being different from what I'm working with.

Good luck

Andrew

[Anonymous]

Hi Andrew,

I certainly think you are on to something with your solution above.

Not sure what I am doing wrong though as with my actual data I am getting the same answer as before, which I don't understand.

Any other ideas please?

Kind Regards

Wendy