Hi All,
I have a dataset which has two date fields “Opened On” and “Week Ending”.
The “Week Ending” dates are for April in the dummy data set (which I have attached). The three values for this field are 4/3/20, 4/10/20, 4/17/20.
The “Opened On” field has dates for April including the weekdays.
I have to create a time series chart as in Sheet 2 of my workbook.
Condition: For each value of “Week Ending” all rows of data for the “Opened On” to be considered except for the Week ending date for the present and previous dates.
Example: for the time series with “Week Ending” on the Column with date 4/10/20, all the “Number of rows” are to be counted when the “Opened On” date is 4/4,4/5,4/6,4/7,4/8,4/9. The count of the number of rows is 26.
Since the count uses the 6 days between the two "Week Ending" date, I tried using the formula -
COUNT(if [Opened On] = ([Week Ending] -1) or
[Opened On] = ([Week Ending] -2)
or [Opened On] = ([Week Ending] -3)
or [Opened On] = ([Week Ending] -4)
or [Opened On] = ([Week Ending] -5)
or [Opened On] = ([Week Ending] -6)
then ([Number of Records]) END)
However, I am getting the value as 19 instead of 26 for "Week Ending" 4/10/2020.
Any suggestions will be appreciated.
The desired result should be for “Week Ending”. 4/3/20 the value should be 8
for “Week Ending”. 4/10/20 the value should be 26
for “Week Ending”. 4/17/20 the value should be 15
Thank you all.
Regards,
Rishi
