Qlik Community

QlikView Expressor

Discussion Board for collaboration related to QlikView Expressor.

el_lloydie
New Contributor III

Expression Help

Good morning,

I require some help if possible please.

I have an expression that counts the number of invoices overdue for suppliers - count({<[Days Overdue]={'>0'}>}[$(=CName)])

I am now trying to find the average number of days these invoices are overdue by.

I have tried avg({<[Due Date]-[Payment Date]={'>0'}>) but I get some crazy results returning, even for suppliers that have no invoices overdue!

Any ideas guys?

Thanks in advance!

1 Solution

Accepted Solutions
serj_shu
Valued Contributor

Re: Expression Help

You can exclude negative values with this:

Sum({<[Days Overdue]={'>0'}>}[Days Overdue])/count({<[Days Overdue]={'>0'}>}[$(=CName)])

8 Replies

Re: Expression Help

Try avg({<InvoiceID={"=[Due Date]-[Payment Date]=>0"}>}[Due Date]-[Payment Date])

Replace InvoiceID with whatever field uniquely identifies your invoices.


talk is cheap, supply exceeds demand
el_lloydie
New Contributor III

Re: Expression Help

Thanks Gysbert.

Unfortunately it still isn't working.

Using your method, and replacing InvoiceID with Invoice Number just brings about NULL for every record. The expression now looks like the below:

avg({<[Invoice Number]={"=[Due Date]-[Payment Date]=>0"}>[Due Date]-[Payment Date])

Thanks

Re: Expression Help

It could be a typo while posting here, but your expression is missing a closing curly bracket

Avg({<[Invoice Number]={"=[Due Date]-[Payment Date]=>0"}>} [Due Date]-[Payment Date])

serj_shu
Valued Contributor

Re: Expression Help

Hello, Gareth!

If you already have a number of invoices so all you'll have to calcutale is sum of days.

After that you must divide sum of days with count of invoices and you'll get average value of days with overdue.

So the expression should be like this:

Sum([Days Overdue])/count({<[Days Overdue]={'>0'}>}[$(=CName)])

el_lloydie
New Contributor III

Re: Expression Help

Thanks Sergey

It's still not working as such, some of the suppliers with over due invoices are now coming back with -300 days for instance but it's nearer than I was!

Thanks

serj_shu
Valued Contributor

Re: Expression Help

You can exclude negative values with this:

Sum({<[Days Overdue]={'>0'}>}[Days Overdue])/count({<[Days Overdue]={'>0'}>}[$(=CName)])

el_lloydie
New Contributor III

Re: Expression Help

OK that's cool but there shouldn't be negative values in the first place.

If a supplier has an overdue invoice, it must have a minimum positive value of 1 for overdue days. Anything 0 or - would indicate an invoice being paid before the due date.

el_lloydie
New Contributor III

Re: Expression Help

However, it appears to have worked! Thank you so much.

I really suck at this, it's all the syntaxes that throw me out. I should have stuck to Excel formulas!

I'm not trying to work out how many Retrospective PO's a supplier has had.

Logically this can be done by counting PO's that have a creation date after the invoice date but I've no idea how to write this properly.

Community Browser