Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fabianaiglesias
Contributor

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?

Tags (1)
1 Solution

Accepted Solutions

Re: Prior period sales comparison

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;

1 Reply

Re: Prior period sales comparison

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;

Community Browser