YTD Comparison with Compare Tables

5
(4)

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.

How useful was this post?

Click on a star to rate useful the post is!

Written by


21 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…

  • 11
    FFavreau on December 28, 2018 Reply

    Hi Rikke!
    I am trying to do a Top 15 that display the 15 best account by sum of won opportunity this year AND display last year sum of opportunity and % evolution for these accounts.
    Problem is that I get blank cells in this year sum of won opportunity when customer made amounts last year, but nothing this one even though I ordered by sum of opportunity this year Desc.
    Any idea ?

    TY for providing us your wisdom ^^

  • 12
    Bhavana Gallewale on January 31, 2019 Reply

    hi Rikke,
    I have a little different problem to solve.

    Our fiscal year starts in Oct and ends in Sept. I need to create a current YTD and Prior YTD for the same period measures, e.g. if I am at the end of our Fiscal Year’s Q2 2019 (which ends in March, as Dec is the end of first Quarter of our fiscal year), I need to compare Oct 2018 to March 2019 data to Oct 2017 to March 2018 data.

    I have pulled our fiscal year and our respective fiscal quarters as dimensions. I also have regular weekending dates in the dataset to create any date based bucketing.

    Can you please suggest any ideas how to implement this?

    thank you,
    Bhavana

  • 13
    Nicolas on February 6, 2019 Reply

    Hi,

    I’d like to do a YTD excluding current month vs Last YTD excluding current month (but last year): for instance, if we are in June, I’d like to compare YTD May 2019 (Jan to May 2019) vs YTD May 2018 (Jan to MAy 2018): how to do it?

    Thanks for your support,
    Nicolas

  • 14
    Henry on February 12, 2019 Reply

    Thanks for this 🙂

  • 15
    Anil on May 7, 2019 Reply

    Thank you so much for all the posts in this website. its really helped me in developing good metrics. i am big fan of your blogger.

  • 16
    Priya on May 20, 2019 Reply

    Hey,
    This is like great.
    Just a follow up. I am working on the week to date report (comparing 2 years 2018 vs 2019).
    But SAQL returns me week 1 as Jan1 to Jan 7 instead of Sunday – Saturday( with the default date field). I have attached the code I used to achieve the result here.
    q = load “Data”;
    q = filter q by ‘AccountsWithPolicies.DistributionChannel’ == “Grp1”;
    q_A = filter q by date(‘TDate_Year’, ‘TDate_Month’, ‘TDate_Day’) in [“current year”..”current week”];
    q_B = filter q by date(‘TDate_Year’, ‘TDate_Month’, ‘TDate_Day’) in [“1 year ago”..”current week-1 year”];
    result = group q_A by ‘TDate_Week’ full, q_B by ‘TDate_Week’;
    result = foreach result generate coalesce(q_A.’TDate_Week’, q_B.’TDate_Week’) as ‘TDate_Week’, sum(q_A.’StreetPremium’) as ‘Premium-2019′, sum(q_B.’StreetPremium’) as ‘premium-2018’;
    result = foreach result generate ‘TDate_Week’, ‘Premium-2019’, ‘premium-2018’;
    result = order result by (‘TDate_Week’ asc);
    result = limit result 2000;

    Thank you in advance.
    Priya

  • 17
    Priya on May 21, 2019 Reply

    Hi Rikke,

    In Analytics studio, while creating a report for week to date summary of two consecutive years, the final report took Jan 1 of each year as week start date and Jan 7 as week end date for week one. Is there a code to resove this issue.
    Here for example: with this SAQL code, I generated week to date reports of 21 weeks(start from jan 1 2019 till yesterday) and compared the same for year 2018. But the issue here is I am not seeing the week as Sunday,monday,etc. Instead it took jan 1 of year 2019 as week 1 day 1.
    q_A = filter q by date(‘TransactionDate_Year’, ‘TransactionDate_Month’, ‘TransactionDate_Day’) in [“current year”..”current week”];
    q_B = filter q by date(‘TransactionDate_Year’, ‘TransactionDate_Month’, ‘TransactionDate_Day’) in [“1 year ago”..”current week-1 year”];
    result = group q_A by ‘TransactionDate_Week’ full, q_B by ‘TransactionDate_Week’;
    result = foreach result generate coalesce(q_A.’TransactionDate_Week’, q_B.’TransactionDate_Week’) as ‘TransactionDate_Week’, sum(q_A.’StreetPremium’) as ‘Premium-2019′, sum(q_B.’StreetPremium’) as ‘premium-2018’;
    result = foreach result generate ‘TransactionDate_Week’, ‘Premium-2019’, ‘premium-2018’, ((‘Premium-2019’-‘premium-2018′)/’premium-2018’)*100 as ‘% change’;

    PS: I removed 2018 to check whether the conflict is due to two years in calculation. But for 2019 alone also I have the same issue.

    Please help me with the ideas.
    Thank you in advance Rikke.
    Priya

  • 18
    Krishna Vankayala on June 16, 2019 Reply

    Hi
    Thanks very much… after a lot of searching you blog made it easy & clear for me

  • 19
    James McGuiness on October 15, 2021 Reply

    I am having an issue seeing the “Create Step” piece…. I only see “Create Query” after I create a new dashboard. How do I see the “Create Step”?

    • 20
      Rikke on October 15, 2021 Reply

      It used to be called step. It’s now called query.

  • 21
    chaitanya on September 12, 2022 Reply

    Hi RIkke,
    Thanks for sharing such great solution, is there a way to show above table as a number widget which just show the count of records?

Leave a Reply to Wilma Wijffels Cancel 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.