topic Re: creating aggregation in total table in QlikView

creating aggregation in total table

Tue, 05 Nov 2013 22:19:07 GMT

Hi,

I have to create a total table started from another one which is more detailed, so I'm talking about an aggregation.
My actual table is highlighted in violet in the attachment.
It displays the quantity and the Previous Year Price for each material. Now I have to create the same indicators but at a higher level namely Material_Group.
Being an average, the calculation is made in this way:
First, I have to multiply the quantity by the PY price for each material, then I'll summarize the results and finally I'll divide it by the total quantity. Is in fact the weighted average:
sum(Qty*PY Price)/sum(Qty).
Considering in my table I won't have the detail for material, I think it will be necessary to use some aggregations.
I've tried to do something like this:
(sum(aggr(PY_Price, MAT_MATERIAL_CODE))*
sum(aggr(QUANTITY,MAT_MATERIAL_CODE)))/
sum(QUANTITY)
but I've got an wrong numerator.
Do you have any ideas?

Thank you

Re: creating aggregation in total table

hic — Wed, 06 Nov 2013 06:46:17 GMT

You should probably use

Sum(PY_Price*QUANTITY)/Sum(QUANTITY)

and use MAT_MATERIAL_CODE as dimension.

HIC

Re: creating aggregation in total table

Wed, 06 Nov 2013 10:19:10 GMT

Hi,

I tried but I didn't get any correct results. I'm focusing on getting the numerator value (PY*QTY)

I've attached my application. In the MATERIAL LEVEL block I have all the materials, on the row PY AVG MAT*QTY I calculated the product between PY and QTY.

Now I should do the same in the GROUP LEVEL block, on the row avg*QTY, but I don't get any result.

For example, for January I'm expecting to get the sum of PY AVG MAT*QTY for all materials. It would be 157126.346.

Re: creating aggregation in total table

Wed, 06 Nov 2013 10:35:15 GMT

I did it. It was missing the aggregation for month.

Re: Re: creating aggregation in total table

Wed, 06 Nov 2013 11:03:13 GMT

I wa wrong. It works only for the january e february. For march I got wrong numbers. I don't understand why. It's very strange.

Please, see in the attachment the formula I used.

Re: creating aggregation in total table

Wed, 06 Nov 2013 11:15:48 GMT

The correct formula is

(aggr($(vPYAVGMAT)*sum(TOTAL_QUANTITY), MAT_MATERIAL_CODE, MONTH_MONTH_OF_YEAR))