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]

Hello Pradosh:

Thank you for the response.  Can you expand on the URL encoding please?  Right up front, I do not know how to do URL encoding.

[pradosh_thakur]

Hi perry

what i meant to say was they have lets say we have an url like

www.sharepoint.com/sites/dummy/abc/ my document - 123.xlsx

this url was not working so they encoded it to

www.sharepoint.com%2Fsites%2Fdummy%2Fabc%2F%20my%20document%20-%20123.xlsx

then  it started working properly and it pointed the exact location. not sure what was the issue. May be it is not what you are looking for.

please refer this as well.

HTML URL Encoding Reference

[marcus_sommer]

Maybe you could find here a hint to your issue or some other ways to solve it:

Qlik Web Connectors 2.2.0 Available

https://community.qlik.com/search.jspa?q=sharepoint&type=document

and you will see which people was involved and which you might invite to participate in this discussion or maybe a new one.

- Marcus

[swuehl]

Perry, any chance that you create two QVWs that load the first few lines of your table and the relevant fields (should show the issue, though) using the two approaches to access the table and attach these files here?

[pnn44794]

Thank you Marcus.  I've bookmarked both.  Unfortunately, my Qlikview admins have not downloaded the Qlik Web Connectors and probably won't.  I've looked at the community and have not, at least not yet, found any potential solutions.  Thank you again for your response and providing the links.  I appreciate it.

[pnn44794]

Thanks Pradosh for the reply.  I've bookmarked that link as it will be helpful to have.

[pnn44794]

Hello Stefan:

Sorry for the delay.  It's been a crazy day.  Anyway, I have attached four files.  Two files from each method (loading from Excel file saved to disk in the Datasources folder and loading Excel directly from SharePoint).  In the pivot tables, you will see the % Delay From SLA Totals and Averages number is different between the two methods and this is probably the critical metric in the table.  They are different because we're winding up with different Breach counts.

The attached straight tables are the raw data from each method.  Since they were relatively small, I provided the full datasets.

[pnn44794]

One other piece of information that may help, is Breach is calculated as follows:

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

[swuehl]

My first idea was that in some cases, the condition evaluates to NULL and this will lead to a 0 output.

But you seem to have also the cases where you get 1 when 0 is expected, right?

Could you add some more lines to the script and reload from the SharePoint source?

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,

Num([Order Complete Date]) as NumCompleteDate,

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

Num(OrderDate) as NumOrderDate,

Num(ApprovalDate) as NumApprovalDate

FROM

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

(ooxml, embedded labels, table is Sheet1);

[pnn44794]

Hello Stefan:

Correct in that I am getting 1 when 0 is expected.  An example is Host Name iaasn00009336.

I've added the columns you asked for and have attached an updated table.  Please note that I assumed you meant Order Create Date instead of OrderDate and Approval Complete Date instead of ApprovalDate.  I made those changes and reloaded from SharePoint.