date ranges: best tool for defining rolling 8 weeks

[dkgoydan]

The output I want is bucketed into weeks, I want the last 8 weeks worth of data using a dynamic formula to define the weeks.

Which tool would I use or what syntax should I use to get the data bucketed like this?

last week (BETWEEN ((DATE - 1) - 6) AND (DATE - 1)
week -2 (BETWEEN ((DATE - 1) - 13) AND (DATE - 7)
week -3 (BETWEEN ((DATE - 1) - 20) AND (DATE - 14)
week -4 (BETWEEN ((DATE - 1) - 27) AND (DATE - 21)
week -5 (BETWEEN ((DATE - 1) - 34) AND (DATE - 28)
week -6 (BETWEEN ((DATE - 1) - 41) AND (DATE - 35)
week -7 (BETWEEN ((DATE - 1) - 48) AND (DATE - 42)
week -8 (BETWEEN ((DATE - 1) - 55) AND (DATE - 49)

[ShankerV]

Hi @dkgoydan 

Step 1: Use the Datetimenow() in formula tool to get the current date.

Step 2: Then create column D1 using datetimeadd tool to subtract the date from current date using formula tool ((DATE - 1) - 6) 

Step 3: Then create column D2 using datetimeadd tool to subtract the date from current date using formula tool (DATE - 1)

Step 4: Use the multirow formula tool to create for next 7 times

Step 5: Create a dynamic formula using the created dates dynamically based on the Datetimenow()

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[ShankerV]

Hi @dkgoydan 

Please find the below expected result which will work dynamically for the date run on the current day.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @dkgoydan 

Breaking down my solution for explaining how my solution works dynamically.

Date time tool is used to take the current date.

Record ID and Generate rows are used to create 8 rows.

Formula tool is used to create -7days and -1days.

Multirow tool is used to create the -7 and -1 applied for all the remaining rows.

Cross tab and Transpose tool are used to convert from columns to rows, so that I can apply the formula.

The dynamic weeks created are appended to the input.

The formula is applied based on the dynamic dates created.

Here is out result.

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V