Collect record for previous day to Calculate variance
Main problem: I am attempting to collect the record of Daily absent for the previous day, I get incorrect argument error. I could use any help.
My end goal is to use the previous day record against the daily absent record to get the variance in numbers and percentage. I would appreciate any other intuitive way of doing that, if there is any.
Thanks
Best Answer
-
Hey @Shile
=JOIN(COLLECT([Daily Absent]:[Daily Absent], Date:Date, Date@row-1))
Unless you need the range to be confined to rows 1 through 9 you don't need the row numbers. This will keep your range dynamic as the column grows. The Join delimiter is an optional term so I didn't include in my formula above as you are only pulling in one value.
You post suggests you will ultimately 'do math' with the value in the [Previous Days Absent No] column. Consider changing the 'JOIN' to a 'MAX' so that the data returned has the characteristics of a Number vs the text characteristics the JOIN produces. If you do use MAX then you will not use a delimiter.
cheers
Kelly
Answers
-
Hey @Shile
=JOIN(COLLECT([Daily Absent]:[Daily Absent], Date:Date, Date@row-1))
Unless you need the range to be confined to rows 1 through 9 you don't need the row numbers. This will keep your range dynamic as the column grows. The Join delimiter is an optional term so I didn't include in my formula above as you are only pulling in one value.
You post suggests you will ultimately 'do math' with the value in the [Previous Days Absent No] column. Consider changing the 'JOIN' to a 'MAX' so that the data returned has the characteristics of a Number vs the text characteristics the JOIN produces. If you do use MAX then you will not use a delimiter.
cheers
Kelly
-
Hi Kelly, thank you for your post.
But how do I solve the situation where there is no "Date@row-1"
Eg.: The date goes directly from Dec 12th to Dec 18th. This way Date@row-1=17th (of which there is no register.
I have a column of dates that, but sometimes there gaps in those dates and corresponding data in the data column.
I nevertheless want to automatically (formula column) calculate the diference between the most recent regsiter of data with the previous register of data (that is not necesarily the day before the current register date, but the most recent date).
Kind regards and a great weekend
-
Hey, sorry for the delay
Will this formula work for you? It would replace my original formula
=INDEX([Daily Absent]:[Daily Absent], MATCH(MAX(COLLECT(Date:Date, Date:Date, <Date@row)), Date:Date, 0))
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!