{"id":1471,"date":"2023-10-20T17:12:36","date_gmt":"2023-10-20T16:12:36","guid":{"rendered":"https:\/\/editjournal.redakt.eu\/faxmodem\/?p=1471"},"modified":"2023-10-25T19:17:03","modified_gmt":"2023-10-25T18:17:03","slug":"year-on-year-yoy-charts-google-looker-data-studio","status":"publish","type":"post","link":"https:\/\/editjournal.redakt.eu\/faxmodem\/blog\/tools\/year-on-year-yoy-charts-google-looker-data-studio\/","title":{"rendered":"Year on Year (YoY) charts in Google Looker Studio"},"content":{"rendered":"<p>If you take data that has date and time and goes back several years and plot it on a chart, the current year is likely to lag behind in numbers, because unlike other years, it won't have data for all 12 months. This is where the so-called <strong>Year-on-Year<\/strong> or <strong>YoY<\/strong> reports come in handy.<\/p>\n<p>To prepare your data for a YoY report, you will have to discard all the entries from the current point in a year until the end of the year, for every year.<\/p>\n<p>For example, if you're in June 14th 2023 18:05:52 and your data goes back to 2017, you will have to discard the entries from:<\/p>\n<ul>\n<li>2017-06-14 18:05:52 to 2017-12-31 23:59:59<\/li>\n<li>2018-06-14 18:05:52 to 2018-12-31 23:59:59<\/li>\n<li>2019-06-14 18:05:52 to 2019-12-31 23:59:59<\/li>\n<li>2020-06-14 18:05:52 to 2020-12-31 23:59:59<\/li>\n<li>2021-06-14 18:05:52 to 2021-12-31 23:59:59<\/li>\n<li>2022-06-14 18:05:52 to 2022-12-31 23:59:59<\/li>\n<\/ul>\n<p>This will make every previous year comparable to the current.<\/p>\n<p>You can accomplish this using <a href=\"https:\/\/support.google.com\/looker-studio\/answer\/6299685?hl=en\">Calculated Fields<\/a>.<\/p>\n<p>Create a calculated field in your data source with the following formula (where <code>event_datetime<\/code> is your custom column name):<\/p>\n<pre><code>CURRENT_DATETIME() &gt; DATETIME_ADD(\n    event_datetime,\n    INTERVAL (YEAR(CURRENT_DATE()) - YEAR(event_datetime)) YEAR\n)<\/code><\/pre>\n<p>(Set the data type of the new field to Boolean.)<\/p>\n<p>The formula compares <code>CURRENT_DATETIME()<\/code> to values from <code>event_datetime<\/code> brought to the current year (leap year accounted for). If the resulting date and time is in the future, it should not be included into the Year-on-Year report.<\/p>\n<p><strong>Side Note<\/strong>: getting &quot;<em>Out-of-range datetime field in parsing function. Error a089525e<\/em>&quot;? Check if you're passing a valid date to <code>PARSE_DATETIME()<\/code>. If you pass a date that does not exist \u2013 e.g. 2021-02-29 \u2013 you will get the above error.<\/p>\n<p>References:<\/p>\n<ul>\n<li><a href=\"https:\/\/stackoverflow.com\/questions\/14491906\/changing-year-in-mysql-date\/35753359#35753359\">https:\/\/stackoverflow.com\/questions\/14491906\/changing-year-in-mysql-date\/35753359#35753359<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>If you take data that has date and time and goes back several years and plot it on a chart, the current year is likely to lag behind in numbers, because unlike other years, it won&#8217;t have data for all 12 months. This is where the so-called Year-on-Year or YoY reports come in handy. To&hellip; <a class=\"more-link\" href=\"https:\/\/editjournal.redakt.eu\/faxmodem\/blog\/tools\/year-on-year-yoy-charts-google-looker-data-studio\/\">Continue reading <span class=\"screen-reader-text\">Year on Year (YoY) charts in Google Looker Studio<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1118],"tags":[1143],"class_list":["post-1471","post","type-post","status-publish","format-standard","hentry","category-tools","tag-google-looker-studio","entry"],"_links":{"self":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts\/1471","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/comments?post=1471"}],"version-history":[{"count":7,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts\/1471\/revisions"}],"predecessor-version":[{"id":1489,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/posts\/1471\/revisions\/1489"}],"wp:attachment":[{"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/media?parent=1471"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/categories?post=1471"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/editjournal.redakt.eu\/faxmodem\/wp-json\/wp\/v2\/tags?post=1471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}