Scripting Relative Dates Based on Latest Date

[aetingu12]

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

[olivierrobin]

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

[sunny_talwar · Accepted solution]

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;

[aetingu12]

Thank you very much.