CODEWITHSUNDEEP
tableau-api
amart47
amart47

Reputation: 95

subtract two numbers in the same window

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

enter image description here

Upvotes: 2

Views: 1836

Answers (1)

ronster1000
ronster1000

Reputation: 570

The easiest solution is to create a new dimension using the 'Lookup' window function.

  1. 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

This should 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.

Upvotes: 2

Related Questions