How to create automate monthly date column in script

[shabarish0587]

Hi Everyone,

                    I have a columns like ID, Response Date, Survey, Recommend and Manual Date.I have a response date this will give all the dates for that month but in dashboard we can show the date as 15th of every month for all the dates, we will get this data monthly, so Every month we can add 15th date manually  and append with existing qvd data. i want to auto generate this date as 15th for every month. can anyone help me on this. i am attaching sample excel file for reference.

[Chernov]

for id =8 , you mean 8 month, not 7?

SET myNewDate = IF( day($1) <= 15, date(monthstart($1)+15), date(monthstart(addmonths($1,1))+15) );

mytable:

load

    *

    , $(myNewDate(dateNum) as dateManual

    ;

load

    id

    , date

    , num(date#(date,'dd/MM/YYYY')) as dateNum

inline [id, date

1, 1/7/2024

2, 2/7/2024

3, 5/7/2024

4, 6/7/2024

6, 7/7/2024

8, 8/8/2024

](delimiter is ',');