rolling 12 month formula
Answers
-
I need help with a average rolling 12 month formula. I can get the 6 month rolling to work as stated below, however I am struggling with a 12 month. Any help would be appreciated.
=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)
-
Have you tried just using a total of 365 days (or 183 in either direction)?
=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= TODAY(183), @cell >= TODAY(-183)), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)
-
@Tina Davis I am thinking would it work if we replace the 6 with 12? I didn't test this formula yet.
=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 12, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 12, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)
-
@Vivien Chong Maybe a +11 and -1.
If you go with 12 in either direction, you'll never get a valid month number.
@Tina Davis Taking a closer look at your formula, I realize you are basically going back 6 months. My formula would be for previous 6 months through next 6 months. If you just want to go back a year, that would look more like
DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1)
This would go inplace of the IFERROR/DATE/DATE you have set for the greater than or equal to portion in your formula.
-
Thank you so much Paul, I was getting a divided by zero that I am looking at my data for accuracy now.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!