Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Prior period sales comparison

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?

1 Solution

Accepted Solutions
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;

View solution in original post

1 Reply
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;