Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Part of a bigger project I am working on, I have to figure out numbers of week between Start and End date. From below screenshot, for each Start_date to End_date I want to figure out weeks in 7 days block. I tried using nested If statement as per below. But, it does not satisfy my need. In a nutshell I want to create two new fields as per the Desire screenshot below -- Weeks and Dates
.
Will appreciate your advice.
Thanks
Sai
Here is another option for you to try
Table: LOAD *, IterNo() as Week, Date(Start_Date + ((IterNo()-1)*7)) as Dates While Start_Date + ((IterNo()-1)*7) < End_Date; LOAD * INLINE [ Year, Period, Start_Date, End_Date 2017, 1, 20/2/2017, 19/05/2017 2017, 2, 19/06/2017, 15/09/2017 2017, 3, 16/10/2017, 19/01/2018 2018, 1, 19/02/2019, 1805/2018 2018, 2, 18/06/2018, 14/09/2018 2018, 3, 15/10/2018, 25/01/2019 2019, 1, 25/02/2019, 24/05/2019 2019, 2, 24/06/2019, 20/09/2019 2019, 3, 21/10/2019, 24/01/2020 2020, 1, 24/02/2020, 22/05/2020 2020, 2, 22/06/2020, 18/09/2020 2020, 3, 19/10/2020, 22/01/2021 ];
Solution suggested here:
Here is another option for you to try
Table: LOAD *, IterNo() as Week, Date(Start_Date + ((IterNo()-1)*7)) as Dates While Start_Date + ((IterNo()-1)*7) < End_Date; LOAD * INLINE [ Year, Period, Start_Date, End_Date 2017, 1, 20/2/2017, 19/05/2017 2017, 2, 19/06/2017, 15/09/2017 2017, 3, 16/10/2017, 19/01/2018 2018, 1, 19/02/2019, 1805/2018 2018, 2, 18/06/2018, 14/09/2018 2018, 3, 15/10/2018, 25/01/2019 2019, 1, 25/02/2019, 24/05/2019 2019, 2, 24/06/2019, 20/09/2019 2019, 3, 21/10/2019, 24/01/2020 2020, 1, 24/02/2020, 22/05/2020 2020, 2, 22/06/2020, 18/09/2020 2020, 3, 19/10/2020, 22/01/2021 ];