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

[effinty2112 · Accepted solution]

Hi Wendy,

The first suggestion I have is to concatenate your two tables to get rid of the large Synthetic Key. So:

[All Docs]:

LOAD

'Invoice' as DocType,

*

Resident TestInvoices;

Concatenate([All Docs])

LOAD

'Credit' as DocType,

* Resident TestCredits;

drop Tables TestInvoices,TestCredits;

Giving this single table with all your fields plus the extra field DocType that identifies each record as being an invoice or a credit.

Create this straight table with these two expressions (this table was filtered to show only a subset of your data to save space).

The expressions sum up the value of the invoices and the credits for the field [L Amount]. A third expression subtracting the second from the first should give you the answer you want.

Ref	Sum(if(DocType = 'Invoice', [L Amount]))	Sum(if(DocType = 'Credit', [L Amount]))
	8962.98	3399.1
270	1613.85	1600.6
446	100.8	0
512	57.6	0
716	496.32	0
2876	1685.4	842.7
2899	1471.98	0
3161	294	353.6
3514	547.2	273.6
3623	657.2	328.6
3868	259.48	0
4032	615.6	0
4150	389.55	0
4311	774	0

Good luck.

Andrew

[Anonymous]

Thanks Andrew,

That looks like a plan.

I will give it a go and let you know the outcome.

Thanks

Wendy

[Anonymous]

Hi Andrew,

That's great Thank You.

I would like to put as a correct answer but for some reason my server does not allow me to do it so I have marked as Helpful.

Much Appreciated

Wendy

[effinty2112]

Hi Wendy,

Very pleased to be of help.

All the best.

Andrew

[Anonymous]

Hi Andrew,

Just wondering if you could help me again on this one.

I now need to count all the unique reference numbers for each month but not quite sure what my expression should be as I need to remove the count of the credits.

I tried Count Count (DISTINCT Ref) -Count (DISTINCT [Credit ID]).

This has not worked as I need to take the [Credit ID] DISTINCT Ref from the overall Count and not sure how to write it.

Many Thanks

Wendy

[effinty2112]

Hi Wendy,

Is this any good?

=Month(Date)	Count({$<DocType = {'Invoice'}>}DISTINCT Ref)	Count({$<DocType = {'Credit'}>}DISTINCT Ref)
	158	132
Jan	53	0
Feb	40	0
Mar	34	0
Apr	35	0
May	36	0
	0	132

The first column is a calculated dimension. The second and third columns give the number of unique refs per month for invoices and credits respectively. There are no credits with a date.

I just noticed that there are an awful lot of records with null dates in the data you uploaded yesterday.

The Table Viewer tells us that only 0.44% of the records have a date value.

I realise that the data you sent may only be a sample and that the real dataset might be in better shape.

Here's something else

=Month(Date)	Count({$<DocType = {'Invoice'}>} DISTINCT Ref)	Count({$<DocType = {'Invoice'}>} DISTINCT Ref)
	158	198
Jan	53	53
Feb	40	40
Mar	34	34
Apr	35	35
May	36	36
	0	0

These expressions look identical but they have different totals. That is because:

The first total has the Total Mode set as follows (the normal choice)

In this case the total shown will be the total number of unique refs across all months.

The other column has the Total Mode set to Sum of Rows, adding up the individual results from each month, which in this case is a greater total than the first column. There must be some refs that crop up in more than one month.

Cheers

Andrew

[Anonymous]

Hi Andrew,

Thank you for your assistance with this.

Yes I think it is because I did some test data which is why all the information is not there.

I am almost there with this one but my calculations are slightly out.  The reason being I think the total is deducting all the References where there is a credit note.  In some instances I only have a part credit, in which case I need to only deduct the references which credit the invoice in full.

I think I probably need some kind of (if statement) so that the calculation only counts credits which cancel the invoice in full.

My current expression is as follows.

Count ({$<DocType= {'nvoice'}>} Ref) - Count ({$<DocType= {'[Credit]'}>} Ref)

Would you be able to help me include an if statement as I need the count to be correct to enable me to obtain a correct average cost.

Many Thanks

Kind Regards

Wendy

[Anonymous]

Hi Andrew,

Please ignore the previous comment I have made.  I have been looking at this some more and think it is a different reason why I am not coming back to the figures I need.

In your previous explanation you have explained that there may be some references which are the same but appear in different months which makes the total larger than the actual months combined.

I have the following in my report

Jan     2060

Feb     2639

Mar     2187

Apr     2632

May     2337

Total     11586

The result I am looking for is

Jan     2018

Feb     2574

Mar     2118

Apr     2566

May     2310

Total     11586

My total Mode is Expression Total.  How do I get my individual months to calculate correctly and come back to the overall total that I want?

Many Thanks

Kind Regards

Wendy

[effinty2112]

Hi Wendy,

I'll back to you as soon as I can. Currently in Edinburgh celebrating my daughter's 21st birthday today. The funny thing is I can't stand the place. The only good thing out of Edinburgh is the train to Glasgow!

All the best.

Andrew