Forum Discussion
Help needed graphing a count between a date range
Thanks stretcharm, but that does nto get me to the result I need.
As an example, if I have the following 3 data rows...
[Serial], [Sale date], [expiry date]
abc123, 1-May-2016, 1-May-2017 (a 1 year warranty)
efg456, 12-July-2016. 12-Dec, 2016 (a 6-month warranty)
hij789, 25-Oct-2016, 25-Oct-2018 (a 2 year warranty)
Then the quarterly chart of 'units in warranty'would need to result in something like this...
2016 Q1 = 0 Units in warranty
2016 Q2 = 1 unit in warranty
2016 Q3 = 2 units in warranty
2016 Q4 = 3 units in warranty
2017 Q1 = 2 units in warranty
2017 Q2 = 2 units in warranty
2017 Q3 = 1 units in warranty
2017 Q4 = 1 units in warranty
2018 Q1 = 1 units in warranty
2018 Q2 = 1 units in warranty
2018 Q3 = 1 units in warranty
2018 Q4 = 1 units in warranty
2019 Q1 = 0 units in warranty
... which does not happen if I use 'step 6' in your response, as you described it.
basically you need to unpivot your date range and then join with calendar (date dimension) to get the count by month, quarter or year.
Let me know if you need help on how to do this
parry2k, The data is not currently 'pivoted' (as far as I know); it is just the source SQL query results at the moment. As for the "join with calendar (date dimension)"; please tell me more about this, as this is something new to me.