Alternate States: Tracking Sales between selected dates
I have a application that tracks a region's top 10 sales people per month (flag created in script). An additional requirement has been added to track how any month's top 10 people can be tracked in the future. So instead of showing all sales where "FLAG.TOP_10 = 1" over n months, they want the ability to select a start date (i.e. date that determines which sales people were in the top 10 for that month) and an end date (e.g. latest month) to compare their sales to. Initial tests went well and I was able to produce a table like this (code below):
Start Date: Feb'18
End Date: Jun '18
Sales Person, Feb'18 Sales, Jun '18 Sales, Sales Movement, Still in Top 10?
I'm having difficulty translating this expression into monthly trend charts that shows the Start Month followed by the 5 months preceding & including the End Date. The below script shows me the Start Date and End Date but will not show the 4 months prior to the End Date. Any thoughts on where I'm going wrong?
I'm not able to share the app as it contains confidential client data. What I am looking to get is the ability for a user to select 2 dates and:
1. Show the Top 10 Sales Amounts for the first date selected and
2. Show the Sales Amounts for all dates between both dates selected, but only for the people flagged in the Top 10 on the first date selected.
I've tried using an As-Of table in the script but did not have any luck so I'm hoping to achieve this in chart calculations. The script I posted above will display what I need for both dates selected, but I cannot get it to display for all dates in-between the two when building a trend chart.