topic Re: Strange Calculation Results in QlikView

Strange Calculation Results

pnn44794 — Mon, 22 Jan 2018 22:09:14 GMT

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.

Re: Strange Calculation Results

pnn44794 — Fri, 11 Oct 2019 19:39:16 GMT

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

Re: Strange Calculation Results

sunny_talwar — Tue, 23 Jan 2018 03:24:20 GMT

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.

Re: Strange Calculation Results

pnn44794 — Fri, 11 Oct 2019 19:40:05 GMT

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.

Re: Strange Calculation Results

sunny_talwar — Tue, 23 Jan 2018 17:54:50 GMT

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

Re: Strange Calculation Results

pnn44794 — Tue, 23 Jan 2018 18:09:16 GMT

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?

Re: Strange Calculation Results

sunny_talwar — Tue, 23 Jan 2018 18:10:58 GMT

We can give that a shot

Re: Strange Calculation Results

pnn44794 — Tue, 23 Jan 2018 18:52:02 GMT

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.

Re: Strange Calculation Results

sunny_talwar — Tue, 23 Jan 2018 18:53:47 GMT

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

Re: Strange Calculation Results

pnn44794 — Fri, 11 Oct 2019 19:41:06 GMT

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.

Re: Strange Calculation Results

sunny_talwar — Tue, 23 Jan 2018 19:27:51 GMT

I think you might have accidentally added the old sample file.

Re: Strange Calculation Results

pnn44794 — Fri, 11 Oct 2019 19:41:47 GMT

LOL. You are right. I did. Try this one.

Re: Strange Calculation Results

sunny_talwar — Tue, 23 Jan 2018 19:54:27 GMT

The Breach stayed 0 for me using the same exact script that you sent me in the work document

Re: Strange Calculation Results

pnn44794 — Tue, 23 Jan 2018 20:38:58 GMT

Very weird. I wonder if my Qlikview desktop session is somehow corrupted. I'm going to shutdown and reboot and then try a reload and see what happens.

Re: Strange Calculation Results

sunny_talwar — Tue, 23 Jan 2018 20:47:48 GMT

Try that and also check the environmental variables to see if there is any differernce

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Re: Strange Calculation Results

pnn44794 — Tue, 23 Jan 2018 22:14:19 GMT

Ok. I rebooted and added any environment variables you have that I don't. I then did a reload. I get the same wrong results. I then, for kicks, deleted my code and pasted in the code from your sample qvw. I still get the wrong results. Things are showing up as breached when they shouldn't be.

Yet, in your sample qvw, it's correct. Very weird.

Re: Strange Calculation Results

pnn44794 — Tue, 23 Jan 2018 22:32:25 GMT

What's really weird is that it's not consistent or does not appear to be consistent when it gets it wrong. A lot of them are correct.

Re: Strange Calculation Results

sunny_talwar — Wed, 24 Jan 2018 00:11:21 GMT

Can you post an image of equivalent of what I have selected from your app?

Re: Strange Calculation Results

pnn44794 — Wed, 24 Jan 2018 00:48:07 GMT

Will do.

Re: Strange Calculation Results

pnn44794 — Fri, 11 Oct 2019 19:42:32 GMT

I hope this works.