0

Line chart time series with missing dates

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!

3 comments

  • 2
    Avatar
    Zach Kathnelson

    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

  • 0
    Avatar
    Ronald So

    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.

  • 0
    Avatar
    Janice Janczyn

    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

Please sign in to leave a comment.