topic Re: Vendor Aging Report in QlikView

Vendor Aging Report

sjhussain — Sat, 14 Nov 2015 15:55:41 GMT

Hello,

Hope all is well with everyone.

We are working on an vendor aging report (from ACCPAC - Sage 300) and would like some help in figuring out aging in terms of 1-30, 30-60, 60-90, 90+ days over due.

Please find attached the image showing the invoices and vendor balances.

* In this example the amount Due is 355,686.50

* Not Due yet - if due date is less than or equal to 0 then we can show it in another column - this we can achieve

* 0 Days overdue - 4,262.15

* -18 days overdue - 177,230.87

* Total Not Due yet is 181,493.02

* 355,686.40 - 181,493.02 = 174,193.38 invoices due

* The only invoice from the table remaining or close to 174,193.38 is 167,845.27 which is 1-30 days over due.

* We should just show this amount in the 1-30 days column

* 1-30, 30-60, 60-90 and 90+ we can achieve through interval match

The question is that how we can just show the 1-30 days over due column and the amount 167,845.27 in this column for this example?

How can we just show the invoice which is due as we have the list of all the invoices?

Will appreciate if someone can assist us.

Thanks.

Message was edited by: Syed Hussain

Re: Vendor Aging Report

settu_periasamy — Sat, 14 Nov 2015 16:49:43 GMT

Hi,

Try something like

Amount Not Due : sum({<APIBH_INVOICES.nDays={"<0"}>}APIBH_INVOICES.AMTTOTDIST)

Invoice Due : [Amount Due] - [Amount Not Due]

1-30 Days : sum({<APIBH_INVOICES.nDays={">0<=30"}>}APIBH_INVOICES.AMTTOTDIST)

Re: Vendor Aging Report

sjhussain — Sat, 14 Nov 2015 16:55:47 GMT

Settu,

Thanks for the response. What about the case when there are multiple invoices in 1-30 days and only a few would be added as they are outstanding from the remaining balance?

Thanks.

Re: Vendor Aging Report

Mark_Little — Mon, 16 Nov 2015 09:02:25 GMT

Hi,

I have done this kind of report a couple of times, each time I have marked the ageing group in script. Something like below.

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>90,[DueAmount])) as '>90D',

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>60 and Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])<=90 ,[DueAmount] )) as '61-90D',

So on..

this will give you the monthly buckets you can then just sum(>90D) for example.

Mark

Re: Vendor Aging Report

gfisch13 — Thu, 03 Mar 2016 15:28:19 GMT

Hi Mark - I'm trying to adapt the code you show above. Where do you load this to create the buckets? Is this in the script, or possibly individual expressions??

This is very helpful as I'm new to this syntax, any guidance would be appreciated!

Thanks,

George

Re: Vendor Aging Report

settu_periasamy — Fri, 04 Mar 2016 01:59:42 GMT

Hi gfisch13‌,

that is script based ..

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>90,[DueAmount])) as '>90D',

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>60 and Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])<=90 ,[DueAmount] )) as '61-90D',