How to show total value (of expression) as real total of elements

[#1]

Dear All,

In excel, I make the report like below (or refer tab "report" in excel file), by:

- FCT & ACT: statistical data

- ERR = ABS(ACT-FCT)

- BIAS = ERR/ACT

In qlikview, the total of ERR & BIAS are not same as in excel (refer the highlighted value in RED and qlikview file).

So, question is How to show total of ERR as in Excel?

BU	Brand	Product	FCT	ACT	ERR	BIAS
BU 1	Brand X	Product A	100	50	50	100%
BU 1	Brand X	Product B	100	80	20	25%
BU 1	Brand X	Product C	100	120	20	17%
BU 1		Total	300	250	90	36%
BU 1	Brand Y	Product E	100	70	30	43%
BU 1	Brand Y	Product F	100	115	15	13%
BU 1	Brand Y	Product G	100	120	20	17%
BU 1		Total	300	305	65	21%
	Total		600	555	155	28%
BU 2	Brand A	Product H	100	110	10	9%
BU 2	Brand A	Product I	100	90	10	11%
BU 2	Brand A	Product J	100	80	20	25%
BU 2		Total	300	280	40	14%
BU 2	Brand B	Product K	100	30	70	233%
BU 2	Brand B	Product L	100	140	40	29%
BU 2	Brand B	Product M	100	20	80	400%
BU 2		Total	300	190	190	100%
BU 2	Brand C	Product N	100	50	50	100%
BU 2	Brand C	Product O	100	100	0	0%
BU 2	Brand C	Product P	100	110	10	9%
BU 2		Total	300	260	60	23%
	Total		900	730	290	40%
Total			1500	1285	445	35%

[marcus_sommer · Accepted solution]

You need to calculate your ERR on row-level and then aggregate them again. You could do it with:

sum(aggr(fabs(sum({<Type={"FCT"}>} Qty)-sum({<Type={"ACT"}>} Qty)), Product))

- Marcus

[sunny_talwar]

marcus_sommer‌ - Correct me if I am wrong, but wouldn't it be better to add all dimensions in the Aggr() function:

Sum(Aggr(fabs(Sum({<Type={"FCT"}>} Qty)-Sum({<Type={"ACT"}>} Qty)), BU, Brand, Product))

My fear is that the result might be incorrect if Product A is available under both BU 1 and BU 2. What do you think, is my concern valid?

[#4]

Both formulas work well. So, could you please explain why there is no different when we add in BU & Brand?

@Sunny, understand your concern. However, in my case, 1 product is belong to only 1 Brand, and 1 Brand is also belong to only 1 BU. The mapping is 1-1 all the time.

[marcus_sommer]

Hi Sunny,

many thanks for these hint and of course you are right, that all needed dimensions must be included within the aggr-statement. It's rather a lazy habbit from me to use only the most granular dimension for it - and in my main use-cases for the aggr the relation between these dimensions (like article, store or a date) and their prior dimensions are unique - but the most important point (by each expression) is to check if the results are like expected or not.

- Marcus

[sunny_talwar]

Dong Nguyen wrote:

However, in my case, 1 product is belong to only 1 Brand, and 1 Brand is also belong to only 1 BU. The mapping is 1-1 all the time.

If this is always true, then its safe to use Marcus's expression. I would request you to mark his response as the correct answer since he responded to your request first. My post was more of a caution to make sure that you don't fall into a trap where the relation is not 1-1. I have been there many times and that's why wanted to caution you.

[#7]

Thanks both of you