Building a pivot table with TY vs LY with % growth

[seakins]

In a pivot table where I have the year 2021 data showing, how can I show the previous years sales (depending on which year has been selected from the main dashboard) including a % growth in the following columns in the pivot?

[HamzaJ]

Hi @seakins ,

You mean something like this?

including percentage

Sisense has a quick function for this. Add and duplicate the field representing sales. Click on the three dots and select quick functions:

It will automatically change if you filter on a different year

[Ophir_Buchman]

Hi,

Thank you for your question. Looking at this discussion, I do see the need of comparing data from this year to the data of a previous year - Where a previous year might not necessarily be the last one.

I'd recommend a few things:

1. Assuming you are working according to best practices, you should have a date dimension you're using to filter your dates. If you don't, please create one (you can use the following one we've created (https://support.sisense.com/kb/en/article/date-dimension-file)

2. Create a custom table (say: "YearCompare") including a single column (year) with two rows (representing the years you wish to compare)

3. Connect the year column to the date dimension's "Year" column

4. Create a pivot table using the following structure:

4.a. Rows - The branch/country/person (or any other category you'd like to aggregate according to)

4.b. Values - Add 3 values:

4.b.i. A measured value representing year #1 - For example: (SUM(Revenue),<Date Filter>) - Where the filter is on min of "YearCompare"

4.b.ii. A measured value representing year #2 - For example: (SUM(Revenue),<Date Filter>) - Where the filter is on max of "YearCompare"

4.b.ii. A measured value representing growth - For example: (SUM(Revenue),<Date Filter (Max Year)>) / (SUM(Revenue),<Date Filter (Min Year)>)

4.c. Columns - Keep empty

Please also note that you can also change the columns' location by changing the row order (dragging the up or down)

I hope this helps,

Ophir

[seakins]

Thank you @HamzaJ,

I have come across this and used it but was looking to see if the data within the pivot table can be a mixed bag of this year vs last year and a % growth on each section.

If I use what you have said above it keeps all the 2021 data columns on the left and then all the 2020 data columns on the right which makes it a bit of a scrolling left and right game.

With 2020 being a washout, our users are interested in viewing 2021 vs 2019 as it is the most comparable year to go against so also looking for maybe selecting two years (2021 & 2019) to display in the mixed bag of TY vs LY % Incr.

[HamzaJ]

@seakinshmm. I get what you mean.

If you want more customization, you could try using measured filter. For instance you could create a formula:

• (sum(revenue), date) with a filter on date for 2020 (or make it dynamic with this year)
• (sum(revenue), date) with a filter on date for 2019 (or make it dynamic with last year and 1 more for 2 years ago)
• (sum(revenue), date) / (sum(revenue), date) for a percentage

The only thing is that users cannot change these filters, so this could be an hassle.

However making it more dynamic / letting it have more user interaction, might be difficult. Atleast I would not know how. Perhaps there is someone here that can help you out 🙂