YTD Comparison with Compare Tables

20. November 2017 Einstein Analytics, How To 10

I love when my clients inspire me to write blogs. I mean they have real challenges and questions that other companies might very well also have, so why not write down the solutions for more people’s benefit. This time I was asked, “how do I compare year to date (YTD) with the same period last year?”. Well, ask no more! Here’s how you can do it using compare tables – gotta love those compare tables!

In my example, I want to compare won opportunities YTD with won opportunities in the same period last year per industry.

Setup your compare table

To get started open up your dashboard – or create a new one – click “Create Step” and choose the dataset you wish to use.

First, we want to change the chart to be a compare table by choosing “Table Mode” and select “Compare Table”.

As I want to see the comparison per industry I first need to create a grouping choosing Industry as the field.

Once we have that set we need to define two columns; one for won opportunities YTD and one for won opportunities last year same period. Now, I cannot (yet) define the specific date filters I am looking for in compare tables, but I can choose something similar to prep my filters and later modify them in SAQL.

But first, we need to change the existing measure from “Count of Rows” to “Sum of Amount”.

As we want to look at won opportunities for a specific timeframe we need to add two filters. We do this by clicking on the arrow next to the measure and select “Add a Filter”.

 

We want to have a filter specifying to take only won opportunities. So select the won field and select true.

The second filter we want is a date filter. As I mentioned we cannot choose in the UI the filter we actually need, but we can prep for it and later modify it. So I will add another filter selection Close Date and define my date filter as relative to now. I will choose Days and drag the sliders to select from 30 days ago to today.

I am now ready to create my second column, which will be covering the same time period last year. So click the + sign underneath the measure to add another measure. Just as with the previous measure we just select Sum of Amount.

With that done we need to create the same Won equal true filter and a date filter. This time set the date filter to be relative to now and choose from last year to last year.

 

SAQL Hack for Date Filters

Next step is to make sure the date filters are correct. In order to do this, we need to switch to SAQL mode. In the upper right corner where we earlier selected “Table Mode” there is also a button for “SAQL mode”, click on that.

You should now see something like this:

In the second line, we have the filter for YTD. The third line represents the YTD Last Year. Looking at the second line we want to change “30 days ago” to “current year”. As you start writing Einstein Analytics will come up with suggestions for the correct functions. This way we now get the YTD won amount.

In the third line, we want to change the latter “1 year ago” to “current day – 1 year”. This way we select today’s day but tell Einstein Analytics to switch out the year with last year.

We want to make sure our column headers have some meaningful names. So in line five, replace “A” with “YTD” and “B” with “YTD Last Year” and click on “Run Query” to see the changes.

Now click “Done” and you have a new step added to your dashboard. You can now add a graph widget to the canvas and drag your step into it. I’ve chosen a column chart and under the column chart setting set Axis Mode from “Small Multiples” to “Single Axis” that way I get both measures in one chart.

Your chart will instantly update and look something like this:

That’s it! You now have a chart that compares YTD with the same period last year.


10 thoughts on “YTD Comparison with Compare Tables”

  • 1
    Wilma Wijffels on November 20, 2017 Reply

    Hey Rikke,
    this is useful!!

    Greetings from Fuji.
    How was San Francisco?

    • 2
      Rikke on November 20, 2017 Reply

      Good to hear Wilma – it was actually with you in mind I did this blog! San Francisco was great! Hope you are well!

  • 3
    Zach on December 1, 2017 Reply

    I’m going to try this out to see if it works with date fields. I’m wondering if I can get a chart to highlight the time between two activities on a team, such as when an account is created, and then the invoice is sent out, to easily see the difference in days

  • 4
    Miguel Zea on March 5, 2018 Reply

    Hi Rikke!

    Thanks a lot, I was trying to match the same periods in two years and with this I can solve it.

    In my case I have built a third column where i show the % of YtD. (C= A/B -1). In the compare table I can choose format Percentaje but once I´ve edited the query I´can´t modified this format.
    Can you help in the “formula” to change numbers format in Query?

    Thanks in advance and sorry for my English XD

    Miguel

    • 5
      Rikke on March 6, 2018 Reply

      Try to do all you need in the compare table before you edit the saql. Once you switch to saql the compare table freezes. There is also a prebuilt function in compare table to see percentage change.

  • 6
    Simon on April 25, 2018 Reply

    Brilliant solution to a problem I have been having this week!

  • 7
    Renato Fusco on July 16, 2018 Reply

    Thank you, Rikke! It was really helpful (=

  • 8
    FFavreau on October 18, 2018 Reply

    Hello Rikke!
    I have a client that want to be able to compare sum of orders for a period and the same period last year like in your exemple. Except he also want to use a list/toggle to switch between periods ( days before, previous week, previous month, Month to date). Is that even possible ?
    I proposed a dashboard with one period by page as a solution ….
    Thanks for your blog!

    • 9
      Rikke on October 18, 2018 Reply

      Have a look at filter bindings and a static step. Or if you do the calculation on the data layer you can just filter by that field.

      • 10
        FFavreau on October 18, 2018 Reply

        Thanks for your quick answer.
        My tables look like that
        |Amount for “DateRange’ this year | Amout for ‘DateRange ‘ last year|
        Product1 | 24 | 36 |

        If i use filtering on lenses and produce one lens by daterange and then use pages to navigate between date ranged steps.
        I tried binding and filtering but it change both values on the table. I did not find a way to filter on a daterange (day , week month) for a column and same range for previous year in the other…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.