I have a dataset like this;
| date | weight |
| 2018-09-01 | 82.0 |
| 2018-09-02 | 82.3 |
| 2018-09-03 | 82.1 |
| 2018-09-15 | 82.3 |
| 2018-09-16 | 83.1 |
| 2018-09-29 | 82.3 |
| 2018-09-30 | 82.9 |
The null dates are missing when I create a time series line chart. How can I create a line chart that includes missing dates to accurately show the time series spacing? Ideally, the line would go from date with data to date with data.
Cheers Team!
Hey Greg! Good question, and a common one. Some of our team members suggest that you should NEVER use the dates from a data source on an X Axis for exactly the reason you specify; sometimes dates are missing, and it can paint an inaccurate picture for your users. Instead, they do this:
1) create your own dates on the x axis using the DATERANGE function. This ensures the x axis isn't missing anything. Try this formula:
DATERANGE(DATE_STARTOF(TODAY(),"3",-1),DATE_ENDOF(TODAY(),"3",-1)) **Paste this right into your x axis formula.
Note: this will return Unix/Epoch time values for all of the dates in September. If you'd like to use the current month, you can remove the ",-1". Use the Properties menu options to show the dates in human readable format:
2) Use LOOKUP in your series to match the dates from your data source to your x axis. Try this formula:
LOOKUP("&XAxis", A:A, B:B). **You won't be able to paste this formula in. The first parameter is a reference to your x-axis. Type "&", and choose x axis. Type a comma, then choose the date column from your data. Type another comma, then choose the weight column.
What LOOKUP does is this: It looks at a list of ALL values (all of the dates in September), It finds a MATCH in a specific list of values (the dates that exist in your data source), and when it finds a match, it takes a CORRESPONDING value (the weights corresponding to each date in your data source).
I hope this helps! Let us know,
Zach
But what about the data points that are missing (Say Sept 4?)? The line chart will drop to 0 because LOOKUP function cannot find the corresponding data point.
Hi Ronald, this is the intended behaviour with the LOOKUP function. If you are trying to achieve a different result, you will need a different approach. Please provide details or email support@klipfolio.com.
Thank you,
Janice
