topic Re: Join issue causing summing issues in QlikView

Join issue causing summing issues

Tue, 01 Mar 2016 04:56:10 GMT

We have an ancient Business App. We have salespeople who get credit for split sales based on a percentage. To calculate Invoice Sales, I sum the line items and then multiply the invoice total by each salesperson percent and our data balances fine.

A line of data might look like this

Order#

Salesperson

Percent

InvoiceTotal

SalespersonTotal

XYZ

Salesperson1

10,000

5,000

XYZ

Salesperson2

10,000

5,000

When we want to goto the item level detail we get a bad join

sum({ $< SalesType={'SLS'}, SalesCommissionFlag={'Y'},Date_InvoiceDate={">=$(=date(iDateBeg))<=$(=date(iDateEnd))"}>} (Prod_SKU_SalesTotal *(Percent/100)))

Order#	Salesperson	Percent	Product	Product Total	SalespersonTotal
XYZ	Salesperson1	50	A	4,000	4,000
XYZ	Salesperson2	50	A	4,000	4,000
XYZ	Salesperson1	50	B	6,000	6,000
XYZ	Salesperson2	50	B	6,000	6,000

It seems like it is doing this because it is totaling the line item as 8,000 then applying the percent calculation. If I add DISTINCT before the calculation it seems to work but causes problems elsewhere (like when I don't want detail and drop the salesperson dimension)

sum({ $< SalesType={'SLS'}, SalesCommissionFlag={'Y'},Date_InvoiceDate={">=$(=date(iDateBeg))<=$(=date(iDateEnd))"}>} DISTINCT (Prod_SKU_SalesTotal *(Percent/100)))

Order#	Salesperson	Percent	Product	ProductLineItemTotal	SalespersonTotal
XYZ	Salesperson1	50	A	4,000	2,000
XYZ	Salesperson2	50	A	4,000	2,000
XYZ	Salesperson1	50	B	6,000	3,000
XYZ	Salesperson2	50	B	6,000	3,000

If I put in a Sum Table. All totals fine.

If I put in Pivot Table, Sum totals are wrong

Very complicated, please see attached.

Re: Join issue causing summing issues

Digvijay_Singh — Tue, 01 Mar 2016 05:26:29 GMT

If you don't want salesperson, you can hide the column instead of dropping. If this is the only problem after your own solution, then I think hiding columns should work. It is available in presentation tab of straight table.

Re: Join issue causing summing issues

Tue, 01 Mar 2016 06:09:29 GMT

If you look in the pivot tables, the totals are wrong. In reality, I put these into Pivot tables so I could total the problem with multiple orders. I would rather put these in Sum Total tables which you cannot hide dimensions. Simply hiding the salesperson is not the problem.

My totals only break on orders where we have multiple salespeople. Iff the order only has 1 sales person, you get a proper 1:Many relationship between the OrderHeader and the Products.

When there is a split sale, we have a Many:Many relationship which I am not sure how to solve in this data model.

Originally the data comes in as

Order	Sales1	Percent1	Sales2	Percent2	Sales3	Percent3
xyz123	Joe	50	Bob	50

and I transform it to in my script

Order	Salesperson	Percent
xyz123	Joe	50
xyz123	Bob	50

Not sure if this helps, but the problem lies in the data model or a better formula to calculate the line item and have it total proper and not the Table Dimensions.

Re: Join issue causing summing issues

Digvijay_Singh — Tue, 01 Mar 2016 06:23:15 GMT

Unfortunately I don't have licensed version so cannot see the qvw file attached. Hope other experts can help.

although I would be able to see data model by using binary load but cannot see the pivot table. I am downloading file to do the same but file is very big so taking time, having bad internet today. For attachment you have option to save it with reduced option to keep minimum file size.

Did you try expression using Aggr(Sum..you can play with different dimensions as long as it comply with the dimensions selected in the chart.

Re: Join issue causing summing issues

Tue, 01 Mar 2016 07:01:15 GMT

Here is a reduced version