topic Re: calculation error in set analysis in QlikView

calculation error in set analysis

jason_nicholas — Wed, 25 Nov 2020 16:16:04 GMT

I have a calculation in a table chart which isn't generating the same results as it would if I ran it manually on an Excel sheet. I would appreciate a review and any suggestions on where I am going wrong.

I have a table chart with Month as the dimension, and a calculated expression of:

SUM({$<[Charge Type]={'Event Charge'},Region={'EMEA'}>}[Calculated Total])

[Calculated Total] is an expression in my load script:
LOAD *,
[Item Quantity]*[Item Unit Price]*[Hours] as [Calculated Total] ;

LOAD [Booking ID], //key
Month([Booking Date]) as Month,
[Item Quantity],
[Item Unit Price],
Hours,
Resource as Support,
[Building Time Zone] ,
FROM
[..\DataSources\AV Support 2017.xls]

I use inline loads to group certain [Support] entries into {'Event Charge'} and certain [Building Time Zone] entries into {'Regions'}.

In my table chart, the expression results in a total of $81,096 for the month of January

If I then build a straight table with columns for[Item Quantity], [Item Unit Price] and [Hours]; along with one for [Support] and one for [Booking ID] to ensure a separate line item for each possible chargeable resource, I can then filter by selecting {'Event Charge'}, {'EMEA'} and {'Jan'} from selection objects, and export the resulting table to Excel for manual calculation.

In the Excel doc, I multiplied the [Item Quantity] by the [Item Unit Price] by the [Hours] and sum them together to get a total of $33,646, which is also the total generated from the previous method (which QV is intended to replace). I can't seem to figure out why the function above is generating a total more than twice what the manual calculation generates.

It may be worth noting that the data in [Item Quantity], [Item Unit Price] and [Hours] only comes in alignment with the [Support] resources in question. There are no other dollar amounts in the data set, except for those excluded by the {'Event Charge'} filter.

Re: calculation error in set analysis

Anil_Babu_Samineni — Wed, 22 Feb 2017 19:44:35 GMT

Can you provide Data and expected output over here. I am confused for your whole structure and i understand some what. May be you are calculating a*b*c as d

and then Sum(d) in expression, Could be the cause it gives 2x times.

Better approach may be data looks and expected result should need to check

Re: calculation error in set analysis

jason_nicholas — Wed, 22 Feb 2017 19:58:19 GMT

Unfortunately, I am not permitted to upload content to an external server, so I can't provide a data example. If I can help clarify any part of this, I will.

As for structure, a particular [Booking ID] might contain 3-4 [Support] resources, each with a quantity(a), hour(b), and cost(c) field associated.

Booking ID	Support	Item Quantity	Item Unit Price	Hours
12345	set tech	1	$45	1
12345	strike tech	1	$45	1
12345	event tech	1	$60	2

I calculate a*b*c in my load data to create a single entry [Calculated Total] per line item.

Booking ID	Calculated Total
12345	$45
12345	$45
12345	$120

I then sum all of the [Calculated Total] entries and group them by Month. This example should result in $210. My actual data should result in $33,646, and it does if I run it manually in Excel and apply the same filters that I have listed in my set analysis.

Could you explain why this would more than double the result?

Re: calculation error in set analysis

jason_nicholas — Wed, 22 Feb 2017 20:02:07 GMT

One more clarification:

My source data consists of a number of these booking IDs, each with a select set of [Support] and charge data.

My function is intended to add the total cost for all booking IDs in a given month, where my set analysis conditions exist.

Re: calculation error in set analysis

sunny_talwar — Wed, 22 Feb 2017 20:39:28 GMT

Is there is a possibility of duplicates within your data? Duplicates caused by joins? If would add a RowNo() as Sno. to my table to see if I can find any unwanted duplicates

Re: calculation error in set analysis

sasiparupudi1 — Thu, 23 Feb 2017 08:48:38 GMT

It might be an issue with your mapping tables. Make sure you do not have duplicates in the mapping as qlikview will only return one value using applymap.

I suggest you disable the mapping tables and check the totals.

hth

Sasi

Re: calculation error in set analysis

jason_nicholas — Thu, 23 Feb 2017 16:02:55 GMT

I have no mapping loads in my app. However, I began commenting large sections of my script to find out at which point the function breaks, and I found it to be related to a later load script. I will post a reply and add details about that test, but I wanted to look at something else I found when I tried another path.

This table is an example of one entry where the sum is not totaling correctly. Not every entry has this problem, but the ones that do add up to the difference I am experiencing.

(Need to scroll right for complete chart)

Everything except [Calculated Total] and sum[Calculated Total]) are dimensions. The other two are expressions built on the field [Calculated Total], which is derived from a preceding load (shown in my original post). I have eliminated the set analysis, because I confirmed it is not part of the problem.

Each line item calculates to 65, and the sum for the entire booking is 195. The function I am trying to build will sum up all of these totals (195 in this case) for all bookings over the course of a month (where set analysis is true). What it is actually doing is counting the total 975 for this booking, instead of 195.

I can't see what is calculating to 325. I would expect the total for each line to be 65, because it is summing only one [Calculated Total]. At the very least, it should say 195 if it were summing all [Calculated Total] for the booking.

The chart above was built to highlight the issue. The function itself needs to live on a chart that looks like the one below. The total for Jan (with only one [Booking ID] selected) should be 195.

Another point to note, if I make the last expression of the first chart

sum(distinct([Calculated Total])

the column looks identical to the [Calculated Total] column- which is correct. But if I make the second chart

sum(distinct([Calculated Total])

I only get 65, because it is only looking at unique values.

Re: calculation error in set analysis

jason_nicholas — Thu, 23 Feb 2017 16:05:39 GMT

EDIT: I found that it is specifically the LEFT JOIN here causing the issue. If I remove it, the issue goes away. However, I only want data from this table if the [Booking ID] already exists from the previous loads.

Here is the load script I have further down, where if I remove it, the complication goes away. it seems completely unrelated, and if I add fields for the data here into the charts above, there is no impact on the result. In fact, none of the results from this load appear in that data at all.

left join
LOAD*,
if(MATCH(Selection,'Minimal','Moderate','Severe'),Selection)as [Incident Severity] ;

LOAD *,
if(MATCH(Selection,'Audio Conference','Audio Reinforcement','Control Room Hardware','Display','Network/WiFi','Power/Infrastructure','Presentation','Telepresence'),Selection)as [Affected Technology] ;

LOAD *,
if(MATCH(Selection,'Technician Error','Technology Error','User Error','Technology Error'),Selection) as [Incident Source];

Event_Reference:
LOAD [Booking ID],
Resource as [Event Detail],
Selection,
Notes as [Event Notes],
if(MATCH(Resource,'Incident'),'Incident')as Incident
FROM
[..\DataSources\Event Reference 2017.xls]
(biff, embedded labels, table is Sheet$);

Re: calculation error in set analysis

sunny_talwar — Thu, 23 Feb 2017 17:24:38 GMT

You might be able to use Left Keep() instead of Left Join or use Where Exists([Booking ID]) to avoid doing a 1 to many or many to many join

Re: calculation error in set analysis

jason_nicholas — Thu, 23 Feb 2017 20:29:00 GMT

That did it. I don't understand how it worked, but replacing with LEFT KEEP was the answer.

I noticed that the table loaded with LEFT KEEP remained as a separate table even when I had it set to LEFT JOIN. I am not sure why that is.

Re: calculation error in set analysis

sunny_talwar — Thu, 23 Feb 2017 20:32:36 GMT

Unlike Join, the purpose of KEEP is to keep the tables separate. Read more about it here:

Understanding Join, Keep and Concatenate