I've got a list of samples and would like to chart them, summarized by week number. Some weeks have 0 samples, some weeks have multiple samples. When I create a PivotChart, I can group it to summarize by weeks; however, it only displays weeks with samples and omits weeks with 0 samples. To the undiscerning eye, this makes it look like there have been a lot more samples than there really have been. I can create a larger list of all weeks represented and have it look up which weeks have samples, but this gets laborious when I start having multiple different sample types.
Is there a way to create a chart that is grouped by week number that displays all weeks from the start date to the end date, including those with 0 samples?
Here's an example much simpler, but analogous to my dataset:
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Thanks.
Is there a way to create a chart that is grouped by week number that displays all weeks from the start date to the end date, including those with 0 samples?
Here's an example much simpler, but analogous to my dataset:
| ID | Year | Weeknum | Year.WeekNum | Date | Location |
| 1 | 2017 | 43 | 2017.43 | 26-Oct-17 | LR |
| 2 | 2017 | 44 | 2017.44 | 31-Oct-17 | Kit |
| 3 | 2017 | 51 | 2017.51 | 23-Dec-17 | Kit |
| 4 | 2018 | 1 | 2018.01 | 3-Jan-18 | Kit |
| 5 | 2018 | 1 | 2018.01 | 4-Jan-18 | Kit |
| 6 | 2018 | 5 | 2018.05 | 30-Jan-18 | LR |
| 7 | 2018 | 5 | 2018.05 | 3-Feb-18 | Kit |
| 8 | 2018 | 23 | 2018.23 | 4-Jun-18 | LR |
| 9 | 2018 | 24 | 2018.24 | 16-Jun-18 | Kit |
| 10 | 2018 | 24 | 2018.24 | 14-Jun-18 | Kit |
| 11 | 2018 | 27 | 2018.27 | 3-Jul-18 | Kit |
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Thanks.
