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 Stefan:

I'm going to guess that there's really no way for you to possibly come up with a solution to this or scenario for why this may be happening without having direct access to the systems.  Would that be accurate?  It's okay if it is.  I appreciate the fact that you tried.

[swuehl]

Yes, it looks really weird.

Last try, can you run this script and repost?

Table:

LOAD *,

     If(NumCompleteDate > NumBreachDate,1,0) as BreachTest;

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([Order Create Date]) as NumOrderDate,

Num([Approval Complete Date]) as NumApprovalDate

FROM

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

(ooxml, embedded labels, table is Sheet1);

[pnn44794]

Hmmm.  Interesting results.  Attached is a table of just the fields you provided in the code above.

As an FYI, since my last post and previous to this one, I did try running the load through transformation.  There are 4 columns with blank column names and sporadic data.  I stripped those out and I get the same results as if I had saved the file to disk.  I believe these results are correct as I don't get 1's where I'm expecting a 0.  Still very weird though  And I use the "real" sharepoint address of

[swuehl]

Sorry, what do you mean with 'I did try running the load through transformation.'?

How does your load script looks like?

[pnn44794]

I know you know this, so please bare with me.  When you're in the script editor and you choose Web Files from the Data tab at the bottom of the editor window, you can then specify a URL and once accepted, you can specify if there are embedded labels, file type, etc and the next step is Enable Transformation.  If I choose that, I can delete the 4 columns with no column name.  Make sense?

My load script has multiple tabs since I'm loading multiple data sources.  But this data source is independent of the others and thus has it's own load script tab.  I've attached a Word doc with just this tabs load instructions.

My sincere apologies if this is crucial information.

[swuehl]

Hi Perry,

I am just a little confused and can't really match your FYI comment with the latest excel data you've attached.

Are you saying that you get correct results loading data from SharePoint using the original (or Sunny's Version) script by just using the transformation clause?

Have you used this clause also running my script? (The script you've attached does not contain the num() fields)

[pnn44794]

Hello Stefan:

My apologies for the delay in replying and I have a feeling I owe an even bigger apology.

What I'm saying is that Sunny helped me with a calculation problem and when I tried it using what I consider the normal URL to access an Excel file from SharePoint, I would not get the correct results, but Sunny was getting the right results.  Then I noticed and should have realized, that Sunny was loading the file from a non-SharePoint site using his local C drive.  As an aside, when you use the "normal link" the file opens in Excel Online and not regular Excel.

So, I decided to save the file to a non-SharePoint location (server in Qlikview DataSources folder) and pull it from there.  I got the right results, but that's a manual process.  So I took it a step further and found this thread and tried that.  When I do, it results in a different URL which works as well.  SO, the problem is why doesn't the "normal" SharePoint link work?

With that said, I get it if you don't want to pursue this any further (I did mention above what I've stated as far as links go).  My thought was that someone else is going to have this problem and if there's a solution to using the "normal" SharePoint URL, we should get that published.

Again, my apologies for the confusion and again, I understand if you do not wish to pursue this any further and I truly hope I have not wasted your time.

[pradosh_thakur]

Hi Perry

Can you use this after From and let me know what you are getting

[https://sp006.jpmchase.net/sites%5Cspncvngr%5CShared%20Documents%5CLOB%20Promise%20Date%20Delivery%5...]

OR

[https://sp006.jpmchase.net/sites/spncvngr%5CShared%20Documents%5CLOB%20Promise%20Date%20Delivery%5CG...]

[pnn44794]

Hello Pradosh:

Sorry for the delay.  I've been sick and still recovering.  Anyway,  I think I'm good to go.  I just cannot simply use the "normal" SharePoint URL, i.e. to include the encoding of things like %, etc.  The file, for whatever reason, is being saved to SharePoint from Business Objects and to open the file, it uses Excel Online.  It does give you an option to open straight in Excel like a lot of other Excel spreadsheets do.  So, to me, it's something to do with Excel Online and it's interaction with SharePoint.  Additionally, the file is too large to open in Excel Online which I think causes an additional problem.  So, it try's to open in Excel Online, can't, and then defaults to a regular Excel spreadsheet opening.

Does that make sense?  In the meantime, I do have a work around for it by using the specific SharePoint URL (without the special characters like %).  I appreciate your help and diligence, but I think we've worked this one enough.  I hope you are not offended as that is not my intention.