Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
sum( {<[Supplier Invoice number]={"=[Supplier Invoice passed for payment date]-[Supplier Invoice Tax Point Date]<2"}>} [Supplier Invoice net value])+NUM(sum( {<[Supplier Invoice Status] ={ 'rejected' }>} [Supplier Invoice net value]),'#,##0')
Hello,
The above formula works but.... I only want to take working days into consideration, I assume I need to use Networkdays but where should I put it?
Currently the formula establishes the value of invoices received and either processed or rejected within 2 days of receiving them but if they arrive on a Friday it isn't correct for me to take Saturday and Sunday into consideration when calculating figures for a KPI.
Thanks,
Daniel
Hi, I would do the check in script, you can check day of the week of a date using weekday(), and add (or substract) 2 days to the date for compare. Furthermore, you can create a flag like isOntime storing 1 when it's ok and 0 when it's not, so you can use a simpler expression like:
sum( {<isOntime={1}>} [Supplier Invoice net value])
Hello,
Thank you for your response, I don't beleive your solution would work as we're importing data covering invoices received over many years with many different tax point dates and we check progress against KPIs after 48hrs and 72hrs.
I am betting you have already looked at the Help, but just in case:
https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527
The latter is a Design Blog post on Master Calendars, not sure if this may help or not, but this may give you some ideas that may get you in the right direction if you have not figured things out yet. The Help link does have a bunch of examples associated with it etc. as well.
Regards,
Brett