0 Replies Latest reply: Oct 30, 2012 6:34 AM by domenico calcagno RSS

    Problem with average calculation

      Hi

       

      I have this situation:

       

      SALES

      Key

      Key

      subsidiary+article

      SubsidiaryDateMovClientArticleAmount
      12009999milanabcmilan10/10/20121234abc120
      romedfgrome10/08/20129876dfg130
      12007777naplejklnaple10/06/20124567jkl200
      genoawergenoa10/04/20128901wer350

       

      COST

      KeySubsidiaryDatePurchArticleCost
      12009999milan10/05/2012abc100
      12007777naple10/03/2012jkl150
      12005555genoa10/03/2012wer250
      12008980rome10/09/2012dfg95
      12003875rome09/10/2012dfg100
      12002698naple09/29/2012jkl120
      12006945milan10/01/2012abc105
      12001856genoa09/15/2012wer200

       

      COST_NO_KEY

      Key

      subsidiary+article

      Subsidiary_nkDatePurch_nkArticle_nkCost_nk
      milanabcmilan10/01/2012abc105
      milanabcmilan10/05/2012abc100
      romedfgrome09/10/2012dfg100
      romedfgrome10/09/2012dfg95
      naplejklnaple09/29/2012jkl120
      naplejklnaple10/03/2012jkl150
      genoawergenoa09/15/2012wer200
      genoawergenoa10/03/2012wer250

       

      I have to calculate marginality for every single sales.

       

      The problem is that not all sales are linked to cost, so i have created a second cost table with a created key that connect the single sales to every purchase done by subsidiary for a specific article.

       

      I need to calculate an average cost of the last 10 days before sales. And in case in the last 10 days there's no purchase calculate an average cost of the last three purchases available before sale.

       

      I use this function to calculate the 10 day average cost

      avg(if(datepurch_nk>=date(datemov-10) and datepurch_nk<=datemov,cost_nk)) 
      

       

      i have a function that calculate the first purchase before sales but i can't use this in other aggragation

      max(if(datemov>=datepurch_nk,datepurch_nk)) 
      

       

      COST_NO_KEY is ordered by subsidiary, article and date.

       

      Can someone help me please?

      Thanks