subtract two numbers in the same window

Question

is there a window calculation that I can use to find the year over year difference in sales?

Answer 1

In order to perform a calculation for your query, you can create a new dimension using the 'Lookup' window function.

1. Now, Right-click on the Data pane and choose 'Create Calculated Field'

2. Name your calculated field (for example, 'YOY Diff')

3. Enter the following formula:
   (LOOKUP(ZN(SUM([Sales])),0) - LOOKUP(ZN(SUM([Sales])),-1))/ABS(LOOKUP(ZN(SUM([Sales])),-1))

4. Save the calculated field. Right-click and select 'Compute Using' = [Date] field and choose the 'Year' option

5. Save the calculated field and drag it into the Columns bar at the top next to the 'Year' dimension

And then this would add a column to your table next to each year with that year's 'YOY Diff' for each corresponding month.

Here's a quick explanation of the calculated field formula:

• The LOOKUP window function takes the format LOOKUP(expression, [offset]) and returns the value of the expression with an offset relative to the current row. In this case, our expression is SUM[Sales], so we look at the current value (say, 2018) - the previous value (2017) and divide that difference by the previous value (2017).

• The ZN function replaces NULL values with zeroes to avoid errors.

• The ABS function ensures the % difference will be positive.