topic Re: Expression based on previous row in QlikView

Expression based on previous row

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

i have this example

DATE ID ID2 STATUS

01/01/2016 001 021 D1

13/02/2017 001 022 D1

13/04/2017 001 023 D2

i have made the aggregate by ID and DATE to show the ID by the max(Date) but i want to know if its possible using only the expression(not script option), get the difference in months between records of the same ID (using DATE).

In simple way, get the difference on months between rows with same ID.

Thanks in advance,

Regards.

Re: Expression based on previous row

PrashantSangle — Mon, 16 Oct 2017 09:22:08 GMT

Hi,

check above().

can share your required output so that we can help you with better solution.

Regards,

Re: Expression based on previous row

Anonymous — Mon, 16 Oct 2017 10:22:47 GMT

Hi Prashant,

Above _Function doesn't run well.

i need :

DATE ID ID2 STATUS DIFFERENCE(IN MONTHS)

01/01/2016 001 021 D1 NULL

13/02/2017 001 022 D1 13

13/04/2017 001 023 D2 2

to calculate the difference between rows from DATE field, to records with the same ID.

I hope that helps...

Thanks a lot!

Re: Expression based on previous row

antoniotiman — Mon, 16 Oct 2017 10:45:44 GMT

Hi Paula,

may be this

Alt((Year(DATE)-Above(TOTAL Year(DATE))-1)*12+Month(DATE)+(12-Above(TOTAL Month(DATE))),'Null')

Regards,

Antonio

Re: Expression based on previous row

Anonymous — Mon, 16 Oct 2017 11:10:27 GMT

Thank you Antonio!

It runs very well