topic Re: Qlikview Equivalent to Excel Workdays in QlikView

Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 02:00:37 GMT

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.

Re: Qlikview Equivalent to Excel Workdays

Anil_Babu_Samineni — Sun, 21 Jan 2018 02:15:49 GMT

May be this?

NetWorkDays(RangeMax(AW2, AX2), BD2)

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 02:48:32 GMT

Hey Anil, this is giving me a value of 0. It should be a date. Do we need to wrap some kind of date formatting around it? Here's the calculation I'm using within the load script (do I need to move it out of the load script????):

NetWorkDays(RangeMax([Order Create Date], [Approval Complete Date]), [E2E SLO]) as [SLA Breach Date]

I substituted the column names for the AW, AX and BD column values in Excel.

Re: Qlikview Equivalent to Excel Workdays

sunny_talwar — Sun, 21 Jan 2018 18:23:02 GMT

Try this

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

I tested this with your given example in a text box object by hard-coding and it seems to give the right value

=LastWorkDate(RangeMax(MakeDate(2017, 4, 21), MakeDate(2017, 4, 21)), 32) -> 6/5/2017

=LastWorkDate(RangeMax(MakeDate(2017, 5, 19), MakeDate(2017, 5, 22)), 69) -> 8/24/2017

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 21:14:12 GMT

What does the +1 do in the following snippet from the expression you provided:

.....[Approval Complete Date]), [E2E SLO] + 1) as [SL....

Also, and this is my fault, I really have a three part problem here. I was going to let Anil know and then start a new thread with the full details and sample data. What would you recommend I do? Continue with this thread or start a new one?

Re: Qlikview Equivalent to Excel Workdays

sunny_talwar — Sun, 21 Jan 2018 21:19:10 GMT

I was always getting to one business day before your required day, so I thought adding 1 might do that job . The internal working of the functions might be a little different between Excel and QlikView, but the main thing to note is that you need a LastWorkDate() function.

I really have a three part problem here.

Not sure what you mean? Is this part of a bigger problem? It's upto you to what you want to do... close this and start a new one or continue using this. Just know this, having one very complicated request is sometime very difficult to solve, but if you can break it down into smaller issues, it tends to get simpler. But having said what I just said, sometimes the request is very difficult to broken down. So, you will have to decide what you want to do

Best,

Sunny

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 21:36:29 GMT

Regarding the +1, that makes sense and appears to be working. Here's the rest of the story.

So the initial expression you provided answers the SLA Breach Date question. I then have to determine if the [Order Complete Date] is greater than the [SLA Breach Date] and if it is, then 1, else 0, as Breach.

Then finally, I need to divide Sum of Breach by Count of Host Name (Sum of Breach / Count of Host Name).

Does this make sense? Do you need sample data?

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 21:48:53 GMT

What I'm trying to get to, if this helps, is % of Delay from SLA.

Re: Qlikview Equivalent to Excel Workdays

sunny_talwar — Sun, 21 Jan 2018 21:51:22 GMT

Sample data might help big time

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 21:53:35 GMT

Ok. You'll have it in a few minutes. Thank you.

Re: Qlikview Equivalent to Excel Workdays

swuehl — Sun, 21 Jan 2018 21:54:35 GMT

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.

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 22:00:29 GMT

Big thanks Stefan for the explanation. I appreciate it.

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sat, 12 Oct 2019 23:04:54 GMT

Ok.

Re: Qlikview Equivalent to Excel Workdays

sunny_talwar — Sun, 21 Jan 2018 22:04:39 GMT

I am going to let swuehl‌ help you

Best,

Sunny

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Sun, 21 Jan 2018 22:17:54 GMT

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.

Re: Qlikview Equivalent to Excel Workdays

sunny_talwar — Sun, 21 Jan 2018 23:29:56 GMT

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

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Mon, 22 Jan 2018 00:43:53 GMT

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

Re: Qlikview Equivalent to Excel Workdays

sunny_talwar — Mon, 22 Jan 2018 00:54:32 GMT

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

Re: Qlikview Equivalent to Excel Workdays

pnn44794 — Mon, 22 Jan 2018 01:11:17 GMT

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.