Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
fabianaiglesias
Creator
Creator

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;