1 Reply Latest reply: Jan 14, 2018 5:16 PM by Sunny Talwar RSS

    Prior period sales comparison

    Fabiana Iglesias

      Hi!

       

      I have the data of accounts of income accounts, costs and expenses of a company.

      I need to compare the monthly amount with the amount of the previous month.

      I am importing the data from Excel and I have obtained the following results:

       

      - When I use Date and quantity data, I get the comparison without problems (see Excel Hoja2)

       

      LOAD

          IDCodigo,

          Fecha,

          Monto,

              Previous(Monto) AS MontoAnt,

             (Monto-Previous(Monto)) AS VarMonto

      FROM [lib://datos/prueba1.xlsx]

      (ooxml, embedded labels, table is Hoja1);

       

       

       

      - When I add account IDs, I do not get the correct results (see Excel Hoja1)

       

      LOAD

          Fecha,

          Monto,

              Previous(Monto) AS MontoAnt,

             (Monto-Previous(Monto)) AS VarMonto

      FROM [lib://datos/prueba1.xlsx]

      (ooxml, embedded labels, table is Hoja2);

       

       

      Could someone suggest me an alternative?

        • Re: Prior period sales comparison
          Sunny Talwar

          May be try this

           

          Table:

          LOAD

              IDCodigo,

              Fecha,

              Monto

          FROM [lib://datos/prueba1.xlsx]

          (ooxml, embedded labels, table is Hoja1);


          FinalTable:

          LOAD *,

          If(IDCodigo = Previous(IDCodigo), Previous(Monto)) AS MontoAnt,

          If(IDCodigo = Previous(IDCodigo), (Monto-Previous(Monto))) AS VarMonto

          Resident Table

          Order By IDCodigo, Fecha;


          DROP Table Table;