Creating a Year-Over-Year Chart in Sisense

[Vadim_Pidgornyi]

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.

[DRay]

This is great! Thank you @Vadim_Pidgornyi!

[alexism]

@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

[DRay]

Hi @alexism,

Let's ping @Vadim_Pidgornyi to see if they can offer any suggestions.

[alexism]

My bad, wrong mention, thanks @DRay  😅
@Vadim_Pidgornyi 

[Vadim_Pidgornyi]

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]

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.