Hello:
I'm getting some inconsistent calculation results from my Load script. The following is a snippet of code from the load script:
Num(If(IsNull([Business Days End 2 END ( without Approvals )]), [Business Days End 2 END ( with Approvals )], [Business Days End 2 END ( without Approvals )]), '#0') as [Actual GTI Days],
LastWorkDate(RangeMax([Order Create Date], [Approval Complete Date]), [E2E SLO] + 1) as [SLA Breach Date],
Num(If([Order Complete Date] > LastWorkDate(RangeMax([Order Create Date], [Approval Complete Date]), [E2E SLO] + 1), 1, 0), '#0') as Breach
Sometimes, Breach will be zero or 1 as expected and then for other rows, it is not. I've attached sample data. If you look at, for example, rows 9 - 15, I would expect the values to be all zero's for Breach. In my pivot table chart, I then have an expression column where the calculation is Sum(Breach)/Count([Host Name]). As you're probably guessing, the calculation is not correct in the chart. The idea is to calculate the percent delay from the SLA. As an FYI, while Host Name is a text field, it is used for counting the number of orders, 1 Host Name = 1 Order.
I've also attached export of the pivot table for reference. I'm thinking, for example, that the % Delay From SLA for VSI in the OS Build section should be somewhere around 20% maybe. Would I perhaps need to format some of the date columns being used a certain way? Here are the one's that would be in play and which in the Load script:
Date([Order Complete Date], 'MM/DD/YYYY') as [Order Complete Date]
Date([Order Create Date], 'MM/DD/YYYY') as [Order Create Date],
Date([Approval Complete Date], 'MM/DD/YYYY') as [Approval Complete Date],
As always, thanks in advance for any and all replies. All help is appreciated.
