Strange Calculation Results

[pnn44794]

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.

[pnn44794]

It might help if I attached what I said I was going to attach.    I've attached it now.  Sorry about that.

[sunny_talwar · Accepted solution]

I am getting Breach as 0 if I use this

Table:

LOAD [Product Build End Type],

    [Host Name],

    [Order Create Date],

    [Approval Complete Date],

    [Order Complete Date],

    [E2E SLO],

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

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

FROM

[..\..\..\Downloads\Sample-Calculation-Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

I am going off to sleep, but I can continue looking at this tomorrow.

[pnn44794]

Hello Sunny:

Sorry for the delay.  I think there's still a problem.  I did use your suggested load script changes.  Please note that I've attached "fresh" data which I added one more column to simply for completeness (Service New does not impact the calculations).  As an FYI, "Bespoke" items have fallen off the data set for whatever reason.

The attached data is pre-filtered for Product Build End Type = VSI OS Build Only.  As examples of why I think there may be a problem still, take a look at row 3 and the last 12 rows.  Shouldn't Breach be equal to 0 for those?  That's what I think they should be.

[sunny_talwar]

Running the same script (just added the new Service New column), I am getting 0 for Breach

[pnn44794]

That is weird.  I must have something else going on that's causing the problem.  Would it be helpful for me to provide the full spreadsheet (minus a couple of non-essential columns) and the associated load script?

[sunny_talwar]

We can give that a shot

[pnn44794]

Not sure if I have fully explained why I need Breach and what I'm trying to accomplish, so here goes.

I need to know if an order has missed, aka Breached, its SLA.  So, I'm trying to calculate if it's Breached and assign 1 to Breach and if not, assign 0.  Then in the QV Pivot table, I have an expression of Sum(Breach) / Count(Host Name)  that will give me the percent of orders that have missed their SLA.  One Host Name = 1 Order, 2 Host Name's = 2 Orders, etc.  Does this make sense?

I'll have the data I mentioned above together and attached shortly.

[sunny_talwar]

Yes, it does... I understand what you are trying to do... just not sure why it isn't working for you

[pnn44794]

Ok.  I've attached what is nearly the full data set (a few columns removed for privacy reasons and they should not impact the calculations).  I've also attached the Load Script for the data (Word doc).  You should be able to simply copy and paste it.  Thanks in advance.