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.