Report Builder

[shilpah]

Hi All,

I want to create a 6 weeks avg report which shows day wise data. For eg: I want to show previous 6 weeks Monday data in one worksheet and previous 6 weeks avg Tuesday data in another worksheet. 

I used rolling data and used custom date range as well, but as the weekly rolling data shows for 7 days once, the second week's data changes its day. 

Eg: I am displaying data from 11th April 2022. Technically I want the next week Monday date to be 18th April. But due to the weekly rolling dates, I am getting 17th April in the Monday worksheet. But 17th is Sunday. I am attaching the trend from Report Builder for your reference

Could Someone please advise?

Regards,

Shilpa

[Jennifer_Dungan]

Hi @shilpah 

When I am creating complex Report Builder reports, I don't use Adobe's built-in date ranges much, but set up a hidden tab where I can set up date ranges using Excel date formulas.

This will require 6 pulls for the 6 weeks, but for Mondays you can use:

Monday of "This Week" (not including today) is =TODAY()-WEEKDAY(TODAY()-2)

So to get Monday of Previous Weeks:

=TODAY()-WEEKDAY(TODAY()-2)-7

=TODAY()-WEEKDAY(TODAY()-2)-14

etc...

To get Tuesdays, you should be able to use =TODAY()-WEEKDAY(TODAY()-3)

and again, use the extra -7, -14, etc to get previous weeks

=TODAY()-WEEKDAY(TODAY()-3)-7

=TODAY()-WEEKDAY(TODAY()-3)-14

Now, if you don't want to include this week at all, and only look at last week and before, I think you can use

=TODAY()-WEEKDAY(TODAY())-5

There is all sorts of help in creating these formulas in Excel.. just make sure you test them over a few days/week to make sure the dates returned are what you want...  (or cheat and change your system date to make sure the calculations are working).

Once you have all your dates configured, you can use Report Builder's "Dates from Cells" to point to these dates, and then the report will pull out data from these specific dates... 

and if something does happen, or you need to rerun the report, you can just manually change these fields and re-run the report without having to modify your Adobe connections.

[david-geist]

Totally agree with everything @Jennifer_Dungan outlined above. I ALWAYS use 'Dates from a Cell' and build out the start and end dates I want to use. 

One additional tip - Instead of using the actual TODAY() function in all formulas, reference another cell with the =TODAY(). That way, if you want to re-run the report for previous weeks, you only need to change that one cell (and not all the formulas that include the TODAY() function).