Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Report Builder

Avatar

Level 1

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

hshilps221_0-1653120717672.png

 

 

Could Someone please advise?

 

Regards,

Shilpa

2 Replies

Avatar

Community Advisor

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... 

 

Jennifer_Dungan_0-1663609505442.png

 

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.

Avatar

Level 1

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).