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
-
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:
Â
Â
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.
Jake Raz
·1 year agoalexism 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:
If you want to add more years, duplicate one of the NEXT/PREV ones and change the number to a different multiple of 12.
david-h
·1 year agoHi  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.
Vadim Pidgornyi
OP1 year agoHello,
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
Â
Â
Â
alexism
·1 year agoMy bad, wrong mention, thanks DRay  😅
Vadim_PidgornyiÂ
DRay
·1 year agoHi alexism,
Let's ping Vadim_Pidgornyi to see if they can offer any suggestions.
alexism
·1 year agoDRay 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
·2 years agoThis is great! Thank you Vadim_Pidgornyi!