How to use Lookup functions for Year Over Year Analyses (how to get Month to Date and Year to Date calculations)

[ameliahelland]

Have you ever wanted to calculate month to date and year to date profit when you have data across many dates in a table? As you may or may not already know, this is easy to do within Sigma!

But what if you wanted to compare these values year over year in the same table? Now, that’s a little bit trickier. It requires using some advanced Lookup functions.

In this post, I’ll provide an example with all the steps you need to follow to accomplish the aforementioned.

Let’s get started…

(A) Create a new table using the Plugs Electronics Hands On Lab data

I chose to rename this table “Plugs Electronics HOL Data” (so you’ll see/use this in later formulas).

(B) Set up your groupings

(1) Create a grouping called [Year of Date] with the formula: Year([Date])

• Sort this column in descending order

(2) Create a grouping called [Month of Date] with the formula: Month([Date])

• Sort this column in ascending order

(3) Create a grouping called [Day of Date] with the formula: Day([Date])

• Sort this column in ascending order

(C) Set up your calculations

Under the [Day of Date] grouping level:
(1) Create a new column called[Daily Profit] with the formula:

• Sum([Price] * [Quantity] - [Cost])

(2) Create a new column called [Month to Date Profit] with the formula:

• CumulativeSum([Daily Profit])

(3) Create another new column called [Previous Year's Month to Date Profit] with the formula:

• Lookup([Month to Date Profit], [Year of Date], [Year of Date] + 1, [Month of Date], [Month of Date], [Day of Date], [Day of Date])

Under the [Month of Date] grouping level:
(1) Create a new column called [Monthly Profit] with the formula:

• Sum([Daily Profit])

(2) Create a new column called[Previous Year's Monthly Profit] with the formula:

• Max([Last Year's Month to Date Profit])

(3) Create a new column called [Year to Date Profit] with the formula:

• CumulativeSum([Monthly Profit])

(4) Create a new column called [Previous Year to Date Profit] with the formula:

• Lookup([Year to Date Profit], [Year of Date], [Year of Date] + 1, [Month of Date], [Month of Date])

Finally…

The resulting table looks something like this:

Upon collapsing the [Month of Date] grouping, we can more easily spot-check the lookup results:

Note that the table above doesn’t include any data past today’s date (it’s currently mid-November 2022). That said, as the days go on, the calculations for this month will change to account for new data.

Polishing it off…

Now, what if you wanted to visualize current month to date and year to date values compared to last year’s? In order to pull these insights from the parent table into the child visualization elements, you’ll need to use advanced lookup functions again. Note the differences in the Lookup formulas in the bonus steps below:

(D) Create visualizations

Current Month to Date Profit (vs. Previous Year’s MTD Profit)
(1) Create a child visualization element from the parent table. Choose the “Single Value” visualization type and un-select “Aggregate Values” in the “Value” section of the left-side panel.

(2) Add a new column to the “Value” section using the formula:

• Lookup([Plugs Electronics HOL Data/Monthly Profit], Year(Today()), [Plugs Electronics HOL Data/Year of Date], Month(Today()), [Plugs Electronics HOL Data/Month of Date])

(3) Create a new column under the “Comparison” section using the formula:

• Lookup([Plugs Electronics HOL Data/Previous Year's Monthly Profit], Year(Today()), [Plugs Electronics HOL Data/Year of Date], Month(Today()), [Plugs Electronics HOL Data/Month of Date])

Current Year to Date Profit (vs. Previous Year’s YTD Profit)
(1) Create a child visualization element from the parent table. Change the visualization type to “Single Value” and un-select “Aggregate Values” in the “Value” section of the left-side panel.
(2) Add a new column to the “Value” section using the formula:

• Lookup([Plugs Electronics HOL Data/Year to Date Profit], Year(Today()), [Plugs Electronics HOL Data/Year of Date], Month(Today()), [Plugs Electronics HOL Data/Month of Date])

(3) Create a new column under the “Comparison” section using the formula:

• Lookup([Plugs Electronics HOL Data/Previous Year to Date Profit], Year(Today()), [Plugs Electronics HOL Data/Year of Date], Month(Today()), [Plugs Electronics HOL Data/Month of Date])

Voila!

Just like that, you’ve got a template for year over year analysis. Feel free to use a similar workflow to analyze month to date and year to date cost, revenue, etc…

P.S. this could also be done through a join in a dataset… a topic for another time.

[zwhitcomb]

Hi @ameliahelland! This is very cool. Can you show me how I could use this to show daily value aggregates month-over-month? I’d like to create comparative line vizualizations.

[ameliahelland]

Thanks for reading @zwhitcomb ! I’m excited for you to try this out. With regards to your follow-up question, can you please elaborate on what you’re going for? In particular, I’m wondering:

• What exactly do you mean by “daily value aggregates month-over-month”? A concrete example would be nice.
• In the final result plot, what would you like to show in the X-axis vs Y-axis? Would you like to color by category or no? etc. This information is needed for me to tell you how your data should be structured.

If you provide more details, I can potentially write up another post to go over the steps.

[ameliahelland]

@zwhitcomb assuming you’d like to show a line plot for Month to Date Profit vs. Last Year’s Month to Date Profit… with daily dates in the X axis and profit in the Y axis (no color-by category)…

1. Create a child visualization element from the Plugs Electronics HOL Data table
2. Change the visualization type to “Line” and un-select “Aggregate Values” from the Y-axis section of the left-side panel
3. Create a new Date column under the Columns section of the left-side panel, using the formula:
   • Date(Concat(Text([Year of Date]), "-", Text([Month of Date]), "-", Text([Day of Date])))
4. Drag the new Date column to the X-axis section of the left-side panel, then right-click > “Truncate” > “Day”. To clean up the dates, right-click the column again > “Format” > “Custom…” > “%B %Y”.
5. Drag the columns: Month to Date Profit and Last Year's Month to Date Profit to the Y-axis section of the left-side panel…
6. If you’d only like to see dates where comparison values are available, feel free to right-click the X-axis column > “Filter” > “After” 2020-01-01 00:00:00 (only applicable for the Plugs Electronics HOL example)

Alternatively, if you’d like to show a line plot for End Of Month Profit vs Last Year’s End of Month Profit:

1. Create a child visualization element from the Plugs Electronics HOL Data table
2. Change the visualization type to “Line”
3. Create a new Date column under the Columns section of the left-side panel, using the formula:
   • Date(Concat(Text([Year of Date]), "-", Text([Month of Date]), "-", Text([Day of Date])))
4. Drag the new Date column to the X-axis section of the left-side panel, then right-click > “Truncate” > “Month”. To clean up the dates, right-click the column again > “Format” > “Custom…” > “%B %Y”.
5. Drag the columns Month to Date Profit and Last Year's Month to Date Profit columns to the Y-axis section of the left-side panel, then right-click each column > “Set Aggregate” > “Max” (this will grab the end-of-month profit values).
6. If you’d only like to see dates where comparison values are available, feel free to right-click the X-axis column > “Filter” > “After” 2020-01-01 00:00:00 (only applicable for the Plugs Electronics HOL example)

[zwhitcomb]

Thanks, @ameliahelland!

I’ll try this out and let you know…

[ameliahelland]

You could also create a bar chart comparing this year’s monthly profits to last year’s monthly profits…

1. Create new child visualization element from the parent table
2. Choose the “No Stacking” bar chart type
3. Click the “+” sign in the X-axis section of the left-side panel, enter the formula:
   • Switch([Plugs Electronics HOL Data/Month of Date], 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December")
4. In the left-side panel, right-click the X-axis column (I called it [Month]), and select “Sort” > “Custom Sort”… then, “Sort by” the [Month of Date] column ascending (choose the Min/Max aggregation).
5. Add another column to the Y-axis section of the left-side panel, using the formula:
   • Lookup(Max([Previous Year's Monthly Profit]), [Month of Date], [Month of Date], Year(Today()), [Year of Date])
6. Add a column to the Y-axis section of the left-side panel, using the formula:
   • Lookup(Max([Monthly Profit]), [Month of Date], [Month of Date], Year(Today()), [Year of Date])

Note that the plot will only show sales-to-date in the current month.

[nathan]

@ameliahelland

(1) Create a grouping called [Year of Date] with the formula: Year([Date])

Note that it’s critical to actually use Year() here; I used the handy “Truncate date” functionality from the right-click pulldown, which produces a DateTrunc() formula. But this yields a date/time result, but we require a number type for the Lookup() formula in step C. I was getting a “Wrong argument types for operator +” error until I changed DateTrunc() to Year().