Index Collect including Cross Sheet Date Comparison - Formula Help
I have two sheets. On the current month's data (#2 below), I am trying to pull in previous treatments from the Historical Patient Treatments (#1 below)
- Historical Patient Treatments
- Treatment - Name of Treatment - {Treatment}
- Acct - Customer Account {Acct}
- MRN - Patient Medical Record Number {MRN}
- Newest - A checkbox indicating that this row contains the MAX Treatment-Date for the patient. {Newest}
- Treatment Date {Treatment-Date}
- Current Month Patient Treatments
- Account
- MRN
- TreatDate
The formula that I am trying to build should provide me (from Historical Patient Treatments
- {Treatment}
- matching {Acct} = Account@row
- matching {MRN} = MRN@row
- {Newest} is checked (1)
- {Treatment-Date} is +/- 14 days of the TreatDate@row
I have tried 25 different versions of this formula and keep getting UNPARSEABLE
Thanks in advance
Best Answer
-
It looks like you are using the DATE function incorrectly. Does this do anything for you?
=INDEX(COLLECT{Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= TreatDate@row - 14, {Treatment Date}, @cell >= TreatDate@row + 14),1)
I also notice a bit of an argument issue. You are only pulling in rows where the {Treatment Date} is both greater than TreatDate MINUS 14 days and greater than TreatDate PLUS 14 days at the same time. You may want to flip that second one around to make it less than or equal to instead.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Are you able to provide a screenshot with sample data?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - Does it help if I give you the formula that I think should work? If not, it is going to take me some time to make an example of this as I can't take screen shots of what I am doing since it contains PHI.
=INDEX(COLLECT{Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= DATE(TreatDate@row - 14), {Treatment Date}, @cell >= DATE(TreatDate@row + 14)),1)
-
It looks like you are using the DATE function incorrectly. Does this do anything for you?
=INDEX(COLLECT{Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= TreatDate@row - 14, {Treatment Date}, @cell >= TreatDate@row + 14),1)
I also notice a bit of an argument issue. You are only pulling in rows where the {Treatment Date} is both greater than TreatDate MINUS 14 days and greater than TreatDate PLUS 14 days at the same time. You may want to flip that second one around to make it less than or equal to instead.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks so much @Paul Newcome . This worked. And good catch on the date argument issue.
Here is the formula that worked: =INDEX(COLLECT({Treatment}, {Acct}, Account@row, {MRN}, MRN@row, {Newest}, 1, {Treatment Date}, @cell >= TreatDate@row - 14, {Treatment Date}, @cell <= TreatDate@row + 14),1)
I appreciate you so much!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!