Knowledge Base Article

Creating a Year-Over-Year Chart in Sisense

Creating a Year-Over-Year Chart in Sisense

Summary

This article addresses the issue of creating a year-over-year comparing chart in Sisense that shows a Key Performance Indicator (KPI) for the current calendar year against the previous calendar year. The chart should display KPI data for the current year's months that have passed and the full previous year.

Main Content

Steps to Create a Year-Over-Year Chart

  1. Create a new normalized date column in the table where you have the date dimension. This can be done by duplicating the existing Date column and editing it.


2.Use the following query to create a new date column:

 

 

CREATEDATE(
GETYEAR(NOW()),
GETMONTH([Date]),
GETDAY([Date]) )

3. After creating the new column, proceed to the dashboard and create a line chart with the required dimension.

4. Change the date formatting to "MMM" (or any desired format) and select the value you wish to compare.

5. Add the original date dimension as a break by with year granularity.

Published 06-03-2024
Data Models

7 Comments

  • Jake_Raz's avatar
    Jake_Raz
    ETL
    02-26-2025

    alexism or any other users in the future: here's another solution that doesn't rely on any custom SQL or modifying the eCube, and instead just uses the PREV/NEXT formulas.

    I modified it slightly, for my purposes. If you use the current year as the baseline, you only see the months that've already occurred in the year (e.g. if it's February right now then your chart will only show January and February). Instead, I use last year as the baseline; this way we can see all 12 months in the chart. I'd set the date filter to Last Year, and then use the following formulas:

    • CURRENT YEAR: ( SUM( [value] ) , NEXT( [date] , 12 ) )
    • LAST YEAR: SUM( [value] )
    • YEAR BEFORE LAST: ( SUM( [value] ) , PREV( [date] , 12 ) )

    If you want to add more years, duplicate one of the NEXT/PREV ones and change the number to a different multiple of 12.

  • alexism's avatar
    alexism
    Cloud Apps
    10-25-2024

    DRay do you know if there is a way to achieve a similar Line year over year chart without the need of an extra column? We would basically need to be creating extra columns for each date field our users want to use to compare year to year if not, it's not really practical in any way.

    When trying to create it, I expected that setting up the format of the X-Axis date column to "MMMM" would mean that months of any year would fall into the same X value (but instead, each month is repeated).

    Thanks

  • Vadim_Pidgornyi's avatar
    Vadim_Pidgornyi
    Sisense Employee
    10-26-2024

    Hello,

    I understand this might be a bit frustrating, but to achieve the described results, you'll need a normalized dates column. However, there's a way to simplify this process so you don't have to create this column in each table.

    You can use the date dimension file provided in this article: Date Dimension File.

    Once you import the xlsx file into your data model, you can link it to your existing Fact tables using the date field. This will allow you to create a normalized date column in the dimension table.

    On your dashboard, you can then use the normalized date from the dimension table as the X-Axis and use the original date column from the Fact table as a break-by.

    I hope this helps! If you have any questions or need further assistance, please feel free to reach out.

    https://created-cloud.uk/s/9Hyq8MKniJYm4Jc

     

     

     

  • david-h's avatar
    david-h
    Cloud Apps
    10-31-2024

    Hi  alexism 

    This is also an approach we us in many (many !) projects ! The dim date can work in different flavors (for instance for the use case Vadim is writing about, I usually work with a "month label" instead of shifting dates to current year), but it usually needs some slight work on the date itself, hence selecting a dim date or working on selected date.

    If you ant something more generic, that will work with any date, you can try the pastyear functions, or PREV function if you need more that tone year against the previous one.

    Hope this helps !

    Best, David.

About How-Tos & FAQs

Node avatar for How-Tos & FAQs
228 ArticlesCreated: 3 months agoLatest Activity: 06-23-2025