Analytics Calculations: Totals by Varying timeframes 1
josephmckeown
moderatorPosts: 54Moderator, Lightspeed Staff moderator
Sometimes to measure a dimension's performance, we need to compare it against varying timeframes. How does its sales this month compare to its sales last month? How do its sales this month compare to its sales year-to-date? How do its sales this month compare to its sales for the same month last year?
We can use calculations to build totals around differing timeframes to prepare for these kind of analyses.
As I mentioned in the discussion on best practices, asking for help is one of the best ways to find the best approach for calculations. This way of preparing differing timeframes is very much inspired by an approach that my colleague Max Dunlap developed.
Let's start with the Recent Sales report, but change the date filter to look at the past 13 Months.
Let's change the dimension of Sale Date to say, Top Level category.
Let's also pivot on the Sale Month. This should give us a report giving us monthly totals by category.
Let's also sort so that the most recent months are to the right, and that the oldest month is at the left...
Let's start simply by finding our totals this month.
From our pivot functions, we can use the pivot_index function to highlight one of these columns.
Because we are looking at the previous 13 months, and because we are sorting months from oldest to newest, "this month" will always be in column 13.
So when we save, we get a new column highlighting just the most recent month.
Similarly, we could use the same calculation to pull up last month's sales too...
Only we would look at column 12 instead of column 13...
So far so good, let's save this as a new report...
Now let's see if we can recreate year-to-date. A good way to start, looking at our date-functions, would be to extract the year from the completed month...
So for each column, we can determine which year the sale took place in...
Now this year, must be the largest year on the table, so we could create a pivot-offset list looking at all the years in the row. To do this, we'll start our calculation with brackets...
We'll ask it to look at the extracted years...
...starting in its own row (0)...
...then looking up to 13 rows to the right...
...so when we save...
We get a big long list of all the available years. Now the only thing we need from the list, is to know which year is the highest. From our math-functions, we could use the large function to return the largest value from the list...
Again, we'll start our calculation with brackets...
...asking it to look at our list of years...
...and identifying...
...position one.
So when we save, we now get one number for the current year, and another number for the largest year.
Again, let's save as new
*Note, for the purposes of this discussion, I'm going to remove the "All Years" list from our report, although usually it's best not to delete anything until the final report is completed the way you want.
So, now this becomes a simple if function, if the current year is the same as the largest year, it should be included in our year-to-date totals. If not, it should not be.
So let's build this using brackets...
If the sale year...
...is equal to...
...the largest year...
...then show us the sale-line totals. If not, don't.
Now, when we save...
We get totals for this year, but not for last.
Again, let's save as new...
Now, we want to get the row totals for the sales this year. Again, we can use a pivot_offset list to return all the monthly totals. As usual, brackets...
...copy...
...paste...
...save...
We're almost done. Now that we have the list of monthly totals, all we need is to find the total of this list. We could use "Sum" to do this...
...and by now...
...you know the drill...
...so when saving...
We get one number.
Again, let's save as new...
...and again, as per above, I'm going to delete the list from our report for now
Finally, the same that we did for sales this month and last month, we can now pivot_index...
...the sales from this year...
...oh let's make sure to pull the results from column one. Because the list is reading from left to right, results at the end of the report will be incomplete...
So upon saving...
Also, we could pivot index the sales from our first row, because it will always be sales for the corresponding month last year...
Giving us three comparisons to include against this month's sales...
Let's save this report as new...
From here, the data is done. All that's left is cosmetic...
The "Year" calculations should all be implicit now in our totals, so we can remove them...
...as we can the monthly totals...
...also, we can hide the measures from visualization...
So that our final report is only looking at the measures we want to see...
And so that we can visualize in a meaningful way...
...or compare in other ways...
Calculations Home
We can use calculations to build totals around differing timeframes to prepare for these kind of analyses.
Credit where Credit is due.
As I mentioned in the discussion on best practices, asking for help is one of the best ways to find the best approach for calculations. This way of preparing differing timeframes is very much inspired by an approach that my colleague Max Dunlap developed.
Let's start with the Recent Sales report, but change the date filter to look at the past 13 Months.
Let's change the dimension of Sale Date to say, Top Level category.
Let's also pivot on the Sale Month. This should give us a report giving us monthly totals by category.
Let's also sort so that the most recent months are to the right, and that the oldest month is at the left...
Let's start simply by finding our totals this month.
From our pivot functions, we can use the pivot_index function to highlight one of these columns.
Because we are looking at the previous 13 months, and because we are sorting months from oldest to newest, "this month" will always be in column 13.
So when we save, we get a new column highlighting just the most recent month.
Similarly, we could use the same calculation to pull up last month's sales too...
Only we would look at column 12 instead of column 13...
So far so good, let's save this as a new report...
Now let's see if we can recreate year-to-date. A good way to start, looking at our date-functions, would be to extract the year from the completed month...
So for each column, we can determine which year the sale took place in...
Now this year, must be the largest year on the table, so we could create a pivot-offset list looking at all the years in the row. To do this, we'll start our calculation with brackets...
We'll ask it to look at the extracted years...
...starting in its own row (0)...
...then looking up to 13 rows to the right...
...so when we save...
We get a big long list of all the available years. Now the only thing we need from the list, is to know which year is the highest. From our math-functions, we could use the large function to return the largest value from the list...
Again, we'll start our calculation with brackets...
...asking it to look at our list of years...
...and identifying...
...position one.
So when we save, we now get one number for the current year, and another number for the largest year.
Again, let's save as new
*Note, for the purposes of this discussion, I'm going to remove the "All Years" list from our report, although usually it's best not to delete anything until the final report is completed the way you want.
So, now this becomes a simple if function, if the current year is the same as the largest year, it should be included in our year-to-date totals. If not, it should not be.
So let's build this using brackets...
If the sale year...
...is equal to...
...the largest year...
...then show us the sale-line totals. If not, don't.
Now, when we save...
We get totals for this year, but not for last.
Again, let's save as new...
Now, we want to get the row totals for the sales this year. Again, we can use a pivot_offset list to return all the monthly totals. As usual, brackets...
...copy...
...paste...
...save...
We're almost done. Now that we have the list of monthly totals, all we need is to find the total of this list. We could use "Sum" to do this...
...and by now...
...you know the drill...
...so when saving...
We get one number.
Again, let's save as new...
...and again, as per above, I'm going to delete the list from our report for now
Finally, the same that we did for sales this month and last month, we can now pivot_index...
...the sales from this year...
...oh let's make sure to pull the results from column one. Because the list is reading from left to right, results at the end of the report will be incomplete...
So upon saving...
Also, we could pivot index the sales from our first row, because it will always be sales for the corresponding month last year...
\]
Giving us three comparisons to include against this month's sales...
Let's save this report as new...
From here, the data is done. All that's left is cosmetic...
The "Year" calculations should all be implicit now in our totals, so we can remove them...
...as we can the monthly totals...
...also, we can hide the measures from visualization...
So that our final report is only looking at the measures we want to see...
And so that we can visualize in a meaningful way...
...or compare in other ways...
Calculations Home
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Lightspeed Analytics and reporting consultant
Lightspeed HQ