KDavidP1987
Board Regular
- Joined
- Mar 6, 2018
- Messages
- 51
Greetings all,
I'm running into an issue with part of an INDEX MATCH array and don't know how to resolve it.
Context: Manager asked that I setup a table to calculate the average calls offered to agents on a 30 day rotation against the days they are WFH (work from home). So, I need needed to get the days they were WFH into a table, and the daily totals of calls they take. Then I needed to merge the data (done), and setup a calculation to provide the 30 day average for each day listed as WFH
Here is the code I came up with to calculate this. (it runs, but the numbers don't seem right)
I've broken it down into pieces, and everything works correctly but one section.
It seems the issue is with the Match section. I need it to pull an array of calls offered on days between the date the agent was WFH and going back 30 days. I originally tried using an AND operator in the match function, but that didn't work. How can I specify this range of time in the formula?
Anyone have an idea how I can accomplish this? Or if there is an easier way
I'm running into an issue with part of an INDEX MATCH array and don't know how to resolve it.
Context: Manager asked that I setup a table to calculate the average calls offered to agents on a 30 day rotation against the days they are WFH (work from home). So, I need needed to get the days they were WFH into a table, and the daily totals of calls they take. Then I needed to merge the data (done), and setup a calculation to provide the 30 day average for each day listed as WFH
Here is the code I came up with to calculate this. (it runs, but the numbers don't seem right)
Code:
=IF([@IsWFHDay]="WFH", AVERAGE(INDEX(Table1[[Agent]:[Calls Answered]],MATCH(1,([Agent]=[@Agent])*([Date]<=[@Date])*([Date]>=[@Date]-30),0),3)),"")I've broken it down into pieces, and everything works correctly but one section.
Code:
MATCH(1,([Agent]=[@Agent])*[COLOR=#ff0000][B]([Date]<=[@Date])*([Date]>=[@Date]-30)[/B][/COLOR]It seems the issue is with the Match section. I need it to pull an array of calls offered on days between the date the agent was WFH and going back 30 days. I originally tried using an AND operator in the match function, but that didn't work. How can I specify this range of time in the formula?
Anyone have an idea how I can accomplish this? Or if there is an easier way
Last edited:
