Prior period sales comparison

[Anonymous]

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?

[sunny_talwar · Accepted solution]

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;