Forum Discussion

seakins's avatar
seakins
Data Storage
10-21-2021

Building a pivot table with TY vs LY with % growth

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?

dashboards & reporting
Finance Analytics

4 Replies

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    10-21-2021

    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's avatar
      Ophir_Buchman
      Data Integration
      11-12-2021

      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

  • HamzaJ's avatar
    HamzaJ
    Data Integration
    10-21-2021

    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 🙂

     

  • seakins's avatar
    seakins
    Data Storage
    10-21-2021

    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.