6 Replies Latest reply: Jun 29, 2011 10:27 AM by Sjoerd van den Bergh

# aggr total problem

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:

• 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

QVW file is almost always useful.

Your expression should look something like this

Sum(Aggr(

*

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

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.

• ###### Re: aggr total problem

You can try attaching it to your initial post.

• ###### Re: aggr total problem

Here you go.

Thanks in advance for the effort.

• ###### Re: aggr total problem

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.