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.

[Anil_Babu_Samineni]

May be this?

NetWorkDays(RangeMax(AW2, AX2), BD2)

[pnn44794]

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.

[sunny_talwar]

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

[pnn44794]

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?

[sunny_talwar]

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

[pnn44794]

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?

[pnn44794]

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

[sunny_talwar]

Sample data might help big time

[pnn44794]

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