0

Time series that includes all time periods

I have the following data:

All I need to do is make a graph that shows the number of rows in this data by month, but I want to include all months even if there are no rows in them, showing zeroes for this missing months. For example, it should have 2 for May 2021, 1 for June 2021, and 0 for April 2021. I've found various solutions posted for this like this one but none of them will work for me. Many of the solutions seem very complex and still don't work, so I feel like I must be missing something obvious here. Thanks!

2 comments

  • Avatar
    Parker Selman Official comment

    Hi Aubrey,


    Thanks for the post! 

    To set this up, we'll need to set up the X axis of the graph to show all months, and then map your data onto that axis with the LOOKUP function. 

    To do this, you can use the formula below on your chart's X axis (where sheet1, A:A represents this column of data):


    From there, ensure that you're sorting your X axis from oldest to newest, and that you've selected a date display format in your X axis properties ie: MMM yyyy: 



    Next, you'll want to reference that X axis in a LOOKUP function, along with the GROUP and COUNTDISTINCT functions:


    This formula looks at each entry in your X axis, then looks for a match in a grouped list of your dates (formatted to match your display format set above), and then returns a distinct count of entries in your data for that particular month. 

    Your final output from this will be a chart like this:



    Hope this helps! If you have any questions or require additional support around this, please log a support ticket with us at support@klipfolio.com 

    Best, 

    Parker

  • 0
    Avatar
    Aubrey Holland

    OMG that worked, thank you so much. I was so close to this about 100 times but never could get it quite right.

Please sign in to leave a comment.