0

How to pull chart of past 12 months

I have a table pulled from Google Analytics where the first column is the month (210810) the second column is the number of users for that month.  I want to display the users by month in a bar chart for the previous 12 months, not including the current month.

Here's the formula that I have tried, but this isn't working.

SELECT(SLICE(@B:B),@A:A=DATERANGE((DATE_ADD(today(),"month",-1),(DATE_ADD(today(),"month",-12)))))

Not sure why this isn't working or if there's a better way to do this.

 

5 comments

  • 1
    Avatar
    Janice Janczyn

    Hi Don,

    The simplest way to do this is to

    1. Set your x-axis formula to point to the month column in your data, then on the Properties tab, set Format as to Date/Time, Input Format is Custom, yyyyMM and Display Format is your preference, e.g., MMM yyyy (Aug 2016 in the dropdown). Then click Add Filter, select Condition and from the dropdown select Last full. Then type 12  in the text input box and select Months from the last dropdown.
    2. Set your series to point to the users column in your data.

    That's it!  For more information on using Filters, see How do I filter data? and Filter, Group, Aggregate and Sort Actions.

     

    Happy Dashboarding!
                                 Janice

     

  • 0
    Avatar
    Don Seckler

    Hi Janice, 

    That worked perfectly.

    I have another graph I am trying to display which is based off a table of Google Analytics sessions by source.  There is data for each day and each source.  I am trying to show the data as stacked bars by month for the past 12 months.  I grouped repeating labels and tried the filter you have here, but no data is showing.

     

    Here's a screenshot of the data table.

     

  • 0
    Avatar
    Janice Janczyn

    Hi Don,

    You would follow the same approach described earlier, but your x-axis properties need to reflect your data and how you want the x-axis grouped. In this case, your Input Format is yyyyMMdd (because the dates in your datasource are 20161112, etc) and again the Display Format is your preference, but needs to be month granularity so when you select Group repeating labels, they are grouped as months. 

    Thank you,
                 Janice

  • 0
    Avatar
    Josh Seerattan

    How do you do step 2,  all the months in my selection set is still displaying and I would like it to only show the last 12 months rolling forward each month?

  • 0
    Avatar
    Keely Davison

    Hi Josh,

    On the x-axis, in properties, remember to add a filter.

    Click Add Filter, select Condition and from the dropdown select Last full. Then type 12  in the text input box and select Months from the last dropdown.

    Keely

Please sign in to leave a comment.