Qlikview Equivalent to Excel Workdays

[pnn44794]

Hello:

Is there an equivalent in Qlikview to Excel's Workday function?  For example, I need to convert WORKDAY(MAX(AW2, AX2), BD2) to a Qlikview equivalent.  AW2 is Order Create Date, AX2 is Approval Complete Date and BD2 is E2E SLO (an integer).  So, I take the max of AW2 and AX2 and add the BD2 value to get an SLA Breach date.

NetworkDays might be the way to go, but it's not clear to me how to code this.

Some samples:

Order Create Date = 4/21/2017, Approval Create Date = 4/21/2017 and E2E SLO = 31 should give me a SLA Breach Date of 6/5/2017

Order Create Date = 5/19/2017, Approval Create Date = 5/22/2017 and E2E SLO = 68 should give me a SLA Breach Date of 8/24/17

Make sense?

As always, any and all help will be appreciated.

[swuehl]

Regarding the +1, It will be needed if you don't want to count the first argument date as your potential first working day (which the QV function itself does (apparently in contrary to the Excel function), so you would need take care of that)

I usually use the +1 in the first argument, just in case it can happen to be a weekend.

[pnn44794]

Big thanks Stefan for the explanation.  I appreciate it.

[pnn44794]

Ok.

[sunny_talwar]

I am going to let swuehl‌ help you

Best,

Sunny

[pnn44794]

You may want to check your messages Sunny, but I understand if you think I’ve offended you. That was definitely not my intention. I hope you reconsider and no offense to Stefan when I say that. I always welcome any and all help. It’s typically the last tag line of any discussion I start (you can check).

I pride myself on trying to be the most respective person I can be and I never intentionally try to offend anyone. At the end of the day, I’m human though and I might offend someone without even realizing I have and most definitely without deliberately doing so.

[sunny_talwar]

I would try something like this in the script (Used the expression pointed out by Stefan in the if statement)

If([Order Complete Date] > LastWorkDate(RangeMax([Order Create Date], [Approval Complete Date]) + 1, [E2E SLO], 1, 0) as BreachFlag

and then may be this on the front end

Sum(BreachFlag)/Count([Host Name])

[pnn44794]

Thanks Sunny.  That gives a syntax error.  However, if I write it like the following, no error.  Would you agree it's essentially the same thing other than slightly different?

If([Order Complete Date] > LastWorkDate(RangeMax([Order Create Date], [Approval Complete Date]), [E2E SLO] + 1), 1, 0) as Breach

[sunny_talwar · Accepted solution]

You can use this, or if you go by Stefan's advice, you need this (I missed a parenthesis earlier)

If([Order Complete Date] > LastWorkDate(RangeMax([Order Create Date], [Approval Complete Date]) + 1, [E2E SLO]), 1, 0) as BreachFlag

[pnn44794]

Hello Sunny:

Thank you.  I believe this, in addition to the Sum expression above, resolves my issue.  Of course, I won't know for sure until I have a complete and accurate dataset (same dataset as the percentile question I had), but I think you have it right.

As always, thank you very much for your help.  I appreciate it.

Additionally, thank you to everyone else that responded.