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

    aggr total problem

    Sjoerd van den Bergh

      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:

      tables.png

      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