topic Re: Scripting Relative Dates Based on Latest Date in QlikView

Scripting Relative Dates Based on Latest Date

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

Dear all,

I have a date field SUMMARY_DATE (31/05/2018) and for simplicity in coding, I would like to create another field where I can mark the latest date as zero and the prior as:

SUMMARY_DATE RELATIVE_MONTH

31/05/2018 0

30/04/2018 -1

31/03/2018 -2

28/02/2018 -3

31/01/2018 -4

Ideally within my Script.

Thanks for all the help,

Aksel

Re: Scripting Relative Dates Based on Latest Date

olivierrobin — Thu, 28 Jun 2018 09:19:52 GMT

hello

why not :

loading your date in a table

loading a 2nd table from the previous resident on order by date descending

computing a field with -1*rowno()+1

Re: Scripting Relative Dates Based on Latest Date

sunny_talwar — Thu, 28 Jun 2018 12:34:01 GMT

This might be another way...

Table:

LOAD * INLINE [

SUMMARY_DATE

31/05/2018

30/04/2018

31/03/2018

28/02/2018

31/01/2018

];

FinalTable:

LOAD SUMMARY_DATE,

RangeSum(Peek('RELATIVE_MONTH'), (Year(SUMMARY_DATE)*12 + Month(SUMMARY_DATE))-(Year(Previous(SUMMARY_DATE))*12 + Month(Previous(SUMMARY_DATE)))) as RELATIVE_MONTH

Resident Table;

DROP Table Table;

Re: Scripting Relative Dates Based on Latest Date

aetingu12 — Thu, 28 Jun 2018 13:25:18 GMT

Thank you very much.