1 Reply Latest reply: Nov 29, 2014 9:14 AM by Stefan Kunte RSS

    Sum, Aggr: Accumulate a value per month

      hello,
      I have a problem and I was wondering if you could help me please.

      I would create two field Key1, Key2

      The first Turnover_Janvier do I recover the Turnover January and concatenates with a string [TextConstant].

      The Turnover_February accumulated Turnover_Janvier + Net Month February case.

      The Turnover_Janvier, Turnover_February and must be divided by 1000 and rounded to two digits.

      I did this:
      NoConcatenate
      MSPCHA_S1:
      LOAD *,
      IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro]) as [Turnover_Janvier Euro in January],
      [
      TextConstant] & (IF ([Month] = 'January', IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro]) & ';' as [KEY 1],
      IF ([Month] = 'February', Round ([Turnover excluding VAT in Euro]/1000,0.01) + (IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro])) as [AC Euro February],


      [TextConstant] & IF ([Month] = 'February', Round ([Turnover excluding VAT in Euro]/1000,0.01) + (IF ([Month] = 'January' Round ([Turnover excluding VAT in Euro] /1000,0.01), [Turnover excluding VAT in Euro])) & ';' as [KEY 2]

      Resident
      Extraction_Bil;


      it does not work, I have value [Turnover_January Euro] which is the same as that of [Turnover_February Euro ], but is FALSE, will CA_February I do not know how.

      In my table I have [Turnover_in Euro] field, MONTHS field, Year field.


      Regards

        • Re: Sum, Aggr: Accumulate a value per month
          Stefan Kunte

          Hi,

           

          I think this is due to your else statement in the if clause which is for both expressions the same ([Turnover excluding VAT in Euro]). So the if condition doesn't match and then your else condition is executed. This is probably the reason why you get the same values for both fields.

           

          Why don't you try doing this accumulation automatically?:

          load*,

          if( match(Month ,Previous(Month))  , numsum( TurnoverTmp , peek( ' TurnoverCumulated ' )), TurnoverTmp ) as TurnoverCumulated

          ;

          load*,

          Round ([Turnover excluding VAT in Euro]/1000,0.01) as TurnoverTmp

          Resident Extraction_Bil

          order by Month asc;

           

          hope this helps

           

          Best regards

          Stefan