topic Re: aggr total problem in QlikView

aggr total problem

Tue, 28 Jun 2011 15:42:11 GMT

Hi,

I have got a problem with the aggr function. What I need is the following:

I have got three tables as you can see in amge below:

When selecting a period from table movvalor1 (Month = January and Year = 2009) I need the following calculated:

sum(cantidad facturada_MV) = Quantity invoiced ´
- expression 1 in simple table: sum([Cantidad facturada_MV])
Total cost related from table MOVVAOR2, which calculates the costs (field portes) related to those transactions which are selected. The costs however need to be calculated untill end of the period selected: Sum (portes)
- expression 2 in simple table:sum({<[Nº mov_ producto2]=P([Nº mov_ producto]), NºAñoMesMV2= {'<=$(=(max(NºAñoMesMV)))'},NºAñoMesMV, AñoMesMV, AnoMV, MesMV =>}Portes)
Total quantity checked related from table MOVVAOR2, which calculates values related to those transactions which are selected. The costs however need to be calculated untill end of the period selected. sum([Cdad_ valorada])
- expression 3 in simple table: sum({<Nº mov_ producto2]=P([Nº mov_ producto]), NºAñoMesMV2= {'<=$(=(max(NºAñoMesMV)))'},NºAñoMesMV, AñoMesMV, AnoMV, MesMV =>}[Cdad_ valorada]

Now the problems start:

I then need to do the following calculation: (column(2) *column(1))/column(3). This gives me the required result for both lines and totals if I use sum of rows and if I put value entry as a dimension. I need these values as totals. See scrrenshot below. I have tried everything in order to get the totals but none work.

Any ideas on how to get the totals correct in the three charts??

Let me know if you need more info or if you need the QWV doc to investigate.

Thanks

aggr total problem

danielrozental — Wed, 29 Jun 2011 02:09:35 GMT

QVW file is almost always useful.

Your expression should look something like this

Sum(Aggr(

sum(cantidad facturada_MV)

sum({<[Nº mov_ producto2]=P([Nº mov_ producto]), NºAñoMesMV2= {'<=$(=(max(NºAñoMesMV)))'},NºAñoMesMV, AñoMesMV, AnoMV, MesMV =>}Portes)

sum({<Nº mov_ producto2]=P([Nº mov_ producto]), NºAñoMesMV2= {'<=$(=(max(NºAñoMesMV)))'},NºAñoMesMV, AñoMesMV, AnoMV, MesMV =>}[Cdad_ valorada])

, [N° Cliente], [N° producto])

)

With this you force QlikView to do all calculations al product/customer lever and them add it up.

Re: aggr total problem

Wed, 29 Jun 2011 07:51:23 GMT

Thanks Daniel,

It still doesn´t work however. I had tried similar expressions without luck. Let me know how I can send you the example.

Thanks in advance.

Re: aggr total problem

danielrozental — Wed, 29 Jun 2011 12:36:13 GMT

You can try attaching it to your initial post.

Re: aggr total problem

Wed, 29 Jun 2011 14:02:48 GMT

Here you go.

Thanks in advance for the effort.

Re: aggr total problem

danielrozental — Wed, 29 Jun 2011 14:12:46 GMT

Here you go.

BTW, just a recommendation, I would avoid using characters like "°" in names, also that many spaces, it makes writing expressions that much more difficult and is more error prone.

Also you're using AnoMV as a field in some set analysis expressions while the correct field name is AñoMV.

Re: aggr total problem

Wed, 29 Jun 2011 14:27:55 GMT

Thanks Daniel.