Calculate week over week difference in Pivot Table

[SophieJ]

I have a pivot where weeks are displayed as columns and revenue is the value. The user picks the week numbers to compare revenue, so for instance week 7 and week 8 can be displayed at the same time. I want to add a column that calculates the difference between the chosen weeks. They change every week, so this week i might choose 7 and 8 but then next week 8 and 9. How do I do that in Looker?

[sam8]

Let's assume your data looks like this:

Week (pivoted dimension)	Week 1	Week 2	Week 3
Revenue (measure)	400	300	500

To compare revenue values between different pivot values, you'll want to use table calculations. I'd recommend either pivot_index or pivot_offset. Let's go over the pros and cons of each.

If you were to use pivot_index: 

• To target individual columns, you can use pivot_index. So, you could write a formula like this to compute the second week minus the first week: `pivot_index(revenue, 2) - pivot_index(revenue, 1)`.
• However, pivot_index isn't dynamic. The "2" and "1" here just refer to the second column and the first column, whatever those might be.
• To make pivot_index work for this use case, you'd need to force the user to filter the table down to two weeks - so that the pivot table only ever has two columns. 
• The final table would look something like this: 
  

  Week (pivoted dimension)	Week 1	Week 2	pivot_offset calc
  Revenue (measure)	400	300	-100

If you were to use pivot_offset:

• You can create a table calculation using pivot_offset. This creates a new column next to every column in your pivot table. It might be a lot of extra clutter, or it might be a good amount of information. Your call.
• You can write a calculation that subtracts the previous pivot column value from the current pivot column value like this: `revenue - pivot_offset(revenue, -1)`
• Your final table would look like this:
  

  Week (pivoted dimension)	Week 1	Week 1 - Week 0	Week 2	Week 2 - Week 1	Week 3	Week 3 - Week 2
  Revenue (measure)	400	null	300	-100	500	200

I hope this helps as a starting point!

[sam8]

Note that my answer above relies on Looker table calculations, which aren't available in Looker Studio. I don't think Studio has any way to look across pivot columns 🤔

[sam8]

Okay, I have another idea for Looker Studio. What if we don't use a pivot table at all?

Here's my idea. We create one metric for Week A, one metric for Week B, and one metric that calculates the delta. First, to illustrate my point, I'll show how you'd do this with static weeks.

• First, create a field that only returns values for the first desired week, let's say week 1. `SUM(CASE WHEN Week(date)=1 THEN revenue ELSE 0 END)`.
• Create another field for the second desired week, let's say week 2. `SUM(CASE WHEN Week(date)=2 THEN revenue ELSE 0 END)`
• Finally, create a field that subtracts these two values. `revenue_week_2 - revenue_week_1`.
• You'd get a table identical to the pivot_offset example:
  

  revenue_week_1	revenue_week_2	delta
  400	300	-100

Now let's introduce some user input here. Since we are just using integers for the week numbers, we can create two integer parameters weekA and weekB that users can select from. Then we'll create similar fields to before, but instead of hardcoding the values "1" and "2", now we insert the parameter names "weekA" and "weekB". In the end, you'll get something like this image: