Totals not matching in tables

[#1]

Hi...

I have attached a report herewith.

In the report tab RVV-YTD, I have 3 tables displaying direct marketing, channel marketing and then the total table (total of direct & channel marketing) . The subtotals match for all the fields in the tables i.e. Total = Direct + Channel except for the fields Rev Impact - Placed Orders, Rev Impact - Invoice Rate and Rev Impact - Rev/Inv orders. This is because the fields are computed based on other precalculated fields and not by adding the values.

The only solution that I see for now is to get data individually for each condition from database and then add up in qlikview. But, that would be a very tedious process considering, I currently have 3 queries but to create the combinations i would need to have 9 queries.

If any one can suggest me a method wherein I can get the values from the direct and channel tables and add them up in the third table, it would solve the problem. I am not sure if set analysis can solve this problem.

Regards

Kumar

[#2]

To add...

Actually, the tab RVV-MTD, clearly reflects my problem as to why went about seperating a single pivot table into 3 different tables. The pivot table has subtotals based on expression and not as the sum of the values. Due to this, when multiplied by the total percentage value it gives a totally different value and the value when the rows are individually added is different.

I hope I am clear on the problem description.

Regards

Kumar

[#3]

Isn't there a solution to this in qlikview??

[#4]

I'm unable to follow exactly where your problem lies. It seems like you are getting unintended results, but I don't know what fields are not giving you the results you expect. There are a lot of fields on there. The Variance in Placed Orders field seems correct on the MTD tab.

Could you create a Totals table with the correct numbers and then acombined table that shows where the two tables differ? If your problem comes from QlikView calculating totals incorrectly in your context, then you may have some options using Dimensionality() or something similar, but I'm having a hard time pinpointing the issue, which makes it difficult to provide a good suggestion.

[#5]

Hi Miller,

The fields of concern are the revenue impact fields. The totals dont match up to sum of individual rows displayed. When I export the report in excel, the sum is different from the one displayed at the top of the rows in qlikview. The values are fine for all the fields except the ones shown below. I am not able to decipher the root of the problem.

	Rev Impact-Placed Orders	Rev Impact-Invoice Rate	Rev Impact - Rev/Inv Orders	Total
Total Displayed in Qlikview Report	($134,117)	$1,700	$40,893	($91,524)
	$3,394	$2,739	($55)	$6,079
	($2,634)	$20,580	$3,083	$21,030
	($84,799)	($60,311)	($27,937)	($173,048)
	$107,381	$12,748	$8,790	$128,919
	($17,111)	$4,002	$360	($12,749)
	($100)	($127)	$424	$197
	$76,854	($58,594)	($13,681)	$4,579
	($87,575)	$953	$20,091	($66,530)
Total that should be actually Displayed	($4,589)	($78,010)	($8,924)	($91,524)

Thank you for your help.

Regards

Kumar

[#6 · Accepted solution]

I now see the problem. I think the difference comes when you multiply by the Actual Invoice Rate. When multiplying by the total Actual Invoice Rate, you're not going to get the same thing as when multiplying each record individually.

I think the solution to this is using the Aggr function on CHANNEL and CATEGORY. I tried putting that together, but I'm not getting good results. That seems to be because you already have an Aggr on one of the fields used in the expression (FCST Placed Orders).

Ok, I managed to figure out the solution. I had to place the actual expressions into the expression and remove the Aggr from the FCST Placed Orders expression. Here is the working expression (I think, it gets the -4589 value):

Sum(Aggr((sum(ORDER_COUNT) - (if([Month]='Jan',PJAN,if([Month]='Feb',PFEB,
if([Month]='Mar',PMAR,if([Month]='Apr',PAPR,if([Month]='May',PMAY,
if([Month]='Jun',PJUN,if([Month]='Jul',PJUL,if([Month]='Aug',PAUG,
if([Month]='Sep',PSEP,if([Month]='Oct',POCT,if([Month]='Nov',PNOV,
if([Month]='Dec',PDEC))))))))))))*(sum([Placed Order]))))*
(sum(INVOICE_COUNT)/sum(ORDER_COUNT))*(sum(REVENUE)/sum(INVOICE_COUNT))
, CATEGORY, CHANNEL))

You may be able to use the Expression labels for the expressions that don't already contain Aggrs, but I didn't test that. There may be a way to do embedded Aggr, but I'm not sure. I've attached my solution, it is in a field to the right of your Rev Impact field. I only did the first one, but I expect the other should work the same way.

[#7]

Hey Miller,

Thanks a lot for the explaination...

Actually, I got a work around for the same. I realised that it was returning the different number due to the expression total on the columns instead of sum of the totals. But, sum of totals on fields like percentages returned ridiculous results. So, I just cloned the table and then displayed the fields with expression table in one and with sum of totals in the other. Then, I hid the columns which dint display the correct results.

I had realised the problem with the aggr function and dint know how to go about a second aggregation on the field already aggregated. Thank you for showing me the way to work around those functions.

Really appreciate your help.

Regards

Kumar