YTD Comparison with Compare Tables
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.
Written by
Hey Rikke,
this is useful!!
Greetings from Fuji.
How was San Francisco?
Good to hear Wilma – it was actually with you in mind I did this blog! San Francisco was great! Hope you are well!
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
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
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.
Brilliant solution to a problem I have been having this week!
Thank you, Rikke! It was really helpful (=
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!
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.
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…
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 ^^
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
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
Thanks for this 🙂
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.
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
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
Hi
Thanks very much… after a lot of searching you blog made it easy & clear for me
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”?
It used to be called step. It’s now called query.
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?