14 Replies Latest reply on Sep 28, 2017 2:07 PM by Hari Ankem

# Calculated Fields: Calculate a difference over time

I have an excel sheet detailing the visas issued per year by country and visa type. I would like to identify and visualize countries that have the highest growth or decline in visas issued since the previous year and for every X year increment (where may be every 2,3,4,etc. years).

The code I have written is not computing the correct numbers:

SUM([Count]) - LOOKUP(ZN(SUM([Count])),-1)

I'm not sure what I'm missing here.

• ###### 1. Re: Calculated Fields: Calculate a difference over time

If you need to see the increment over a year, then the year too should be included in the chart.

I have also included an year increment parameter so that you can see the variance compared to prior N year(s). Check out the attached workbook.

Hope this helps.

1 of 1 people found this helpful
• ###### 2. Re: Calculated Fields: Calculate a difference over time

Thank you, that helped out alot. Now lets say I want to see the largest differences in the last year sorted from largest to smallest by country. I believe you could do this by creating a Country filter, going to the "Top" tab, and creating a formula using [YearDiff].

What would that formula be?

• ###### 3. Re: Calculated Fields: Calculate a difference over time

You can create a calculated field to compute the last year's difference as shown below, and then refer that field to sort the data.

I have added that column in a discrete form so that you can see that it's working.

• ###### 4. Re: Calculated Fields: Calculate a difference over time

For some reason when I try to change the visual from bars to lines, my "YearDiff" pill goes red and I cannot use the visual. Is something disconnecting when I try to change visuals?

• ###### 5. Re: Calculated Fields: Calculate a difference over time

I think I fixed it by changing the table calculation and removing the color pill from the measures box

• ###### 6. Re: Calculated Fields: Calculate a difference over time

I would like to display this chart as a percentage based off the previous year, so for example if 2015 had 1,000 visas issued and 2016 has 1,500 visas issued I like that data point to display as a 50% increase instead of the pure number difference of 500.

How would I go about changing this formula?

• ###### 7. Re: Calculated Fields: Calculate a difference over time

Not sure what's missing here.

• ###### 8. Re: Calculated Fields: Calculate a difference over time

Still getting the same error

• ###### 9. Re: Calculated Fields: Calculate a difference over time

I think I figured it out, attached is the twbx if anyone wants to check.

• ###### 10. Re: Calculated Fields: Calculate a difference over time

Hope this is the formula you used:

(ZN(SUM([Count])) - LOOKUP(ZN(SUM([Count])), -[Year Increment]))

/

LOOKUP(ZN(SUM([Count])), -[Year Increment])

• ###### 11. Re: Calculated Fields: Calculate a difference over time

Hari Ankem

If instead of doing a year over year calculation comparing the current year to the last, what if I wanted to compare the most current year to the earliest (Year 2000 in this case)?

I edited the formula to show what I mean.

• ###### 12. Re: Calculated Fields: Calculate a difference over time

Instead of hard-coding the years, it may be better to have a choice of selecting the years which you want to compare. However, in this case, you have to change your chart to not include the Year in the columns anymore. Instead, you could use the Country.

• ###### 13. Re: Calculated Fields: Calculate a difference over time

In this case I would like to keep using the line chart and the x-axis needs to be a function of time. Can I not hardcode the lookup offset to be year 2000?

• ###### 14. Re: Calculated Fields: Calculate a difference over time

You cannot do an offset the way you wrote above. Instead, you can increase the range of the increment parameter and set it to 16 to see the comparison of the values.