topic Re: aggregation problem in QlikView

aggregation problem

Tue, 16 Sep 2014 13:45:00 GMT

Hi all

I want to concatenate 2 table for 2 business units and create a dashboard using that info,but my issue is that they calculations are different for 1 measure.

e.g

Business Unit A

Sale = sum(sale)/Total (sale)

Business Unit B

Sale = avg(Sale)

I tried to sum( (sum(sale)/Total (sale)) + avg(sale)) --------it does not work

or avg( (sum(sale)/Total (sale)) + avg(sale)) -----also does not work

How can I go about doing that so that I can have 1 chart/pivot table for both BUs

Thanking you in advance

Re: aggregation problem

MK_QSL — Tue, 16 Sep 2014 13:47:12 GMT

Can you provide sample data along with your expected result?

Re: aggregation problem

Tue, 16 Sep 2014 14:46:14 GMT

Business Unit A

Product	City	User	Sales
Cheese	Alonso	Ben	23
Butter	Vryburg	Jon	12
Milk	Florida	Peter	23
Bread	Florida	Brian	66
Rice	Alonso	Ben	23
Butter	Orlando	Jon	12
Milk	Florida	Peter	23
Bread	Florida	Brian	66

Calculation = avg(Sales) --- lets assume you have a user as a dimesion

Business Unit B

Product	City	User	Amount	Trans_Type
Rice	Alonso	Ben	23	Sale
Samp	Vryburg	Jon	12	Order
Milk	Florida	Peter	23	Order
Bread	Florida	Brian	66	Return
Rice	Alonso	Ben	23	Sale
Samp	Orlando	Jon	12	Sale
Milk	Florida	Peter	23	Order
Bread	Florida	Brian	66	Order

Calc = (sum({<Trans_Type={'Sale'}>}Sale)/sum(Sale)

now I want an average of sale for both BUs on 1 chart/pivot table

Re: aggregation problem

MK_QSL — Tue, 16 Sep 2014 14:53:41 GMT

Sorry but still having one confusion, What is the difference of Amount for BU A and BU B?

Re: aggregation problem

Anonymous — Tue, 16 Sep 2014 15:11:57 GMT

You need to add "source" columns to the concatenated tables that will point what BU you use in the formula.

And expression might be:

if (Source = 'A')

sum(sale)/Total (sale)

else avg(Sale)

Re: aggregation problem

Anonymous — Tue, 16 Sep 2014 15:56:13 GMT

you need additional one attribute in the table with BU information (A,B). based on this field you can use it in the expression.