Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner - Specialist
Partner - Specialist

Strange Calculation Results

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.

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

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

View solution in original post

48 Replies
pnn44794
Partner - Specialist
Partner - Specialist
Author

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

sunny_talwar

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

Capture.PNG

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

pnn44794
Partner - Specialist
Partner - Specialist
Author

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

Capture.PNG

pnn44794
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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.