topic Re: Remove Credit Notes in QlikView

Remove Credit Notes

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Remove Credit Notes

effinty2112 — Tue, 21 Jun 2016 12:47:57 GMT

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

Re: Remove Credit Notes

Anonymous — Tue, 21 Jun 2016 12:56:58 GMT

Thanks Andrew,

That looks like a plan.

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

Thanks

Wendy

Re: Remove Credit Notes

Anonymous — Tue, 21 Jun 2016 13:15:31 GMT

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

Re: Remove Credit Notes

effinty2112 — Tue, 21 Jun 2016 15:25:47 GMT

Hi Wendy,

Very pleased to be of help.

All the best.

Andrew

Re: Remove Credit Notes

Anonymous — Wed, 22 Jun 2016 11:36:06 GMT

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

Re: Remove Credit Notes

effinty2112 — Wed, 22 Jun 2016 15:46:17 GMT

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

Re: Remove Credit Notes

Anonymous — Tue, 28 Jun 2016 13:34:10 GMT

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

Re: Remove Credit Notes

Anonymous — Tue, 28 Jun 2016 14:46:59 GMT

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

Re: Remove Credit Notes

effinty2112 — Tue, 28 Jun 2016 15:41:37 GMT

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

Re: Remove Credit Notes

Anonymous — Tue, 28 Jun 2016 16:07:23 GMT

Hi John,

OK Thanks your a star.

Hope you enjoy the celebrations.

Kind Regards

Wendy

Re: Remove Credit Notes

Anonymous — Tue, 28 Jun 2016 16:08:42 GMT

Sorry I meant to say Andrew

Just come off the phone from speaking to John

Re: Remove Credit Notes

effinty2112 — Wed, 29 Jun 2016 13:47:59 GMT

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	ID	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	1001943	0	404.55	0	0	0	10136
	Credit	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

Re: Remove Credit Notes

Anonymous — Thu, 30 Jun 2016 09:41:47 GMT

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

Re: Remove Credit Notes

effinty2112 — Thu, 30 Jun 2016 15:12:25 GMT

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([Pat Amount])	Sum([Pit Amount])
		1906.05	1213.65	4811.87	1115.46
Mar	1	635.35	0	1554.44	371.82
Apr	1	0	0	148.55	0
May	1	635.35	809.1	1554.44	371.82
	1	635.35	404.55	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

Re: Remove Credit Notes

Anonymous — Fri, 01 Jul 2016 09:41:41 GMT

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