Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_K
Contributor II
Contributor II

How to only take weekdays into consideration in a sum based on tax point dates

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

Labels (2)
3 Replies
rubenmarin

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])

Daniel_K
Contributor II
Contributor II
Author

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.

Brett_Bleess
Former Employee
Former Employee

I am betting you have already looked at the Help, but just in case:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.