How to apply a variable to specific values in a table?

[#1]

Hello -

I created a simple chart based on the data in the following format: 

Market	Year	Sales
Austria	2008	1,800
Austria	2009	2,000
Austria	2010	2,220
Austria	2011	1,750
Belgium	2008	20,000
Belgium	2009	23,000
Belgium	2010	24,000
Belgium	2011	21,250

In this example, the sales numbers from 2008-2010 are reported in USD, and the 2011 totals are in Euro.  I need to get the 2008-2010 values equivalent to a Euro currency.  I created a variable called "FX Rate" (where FX rate = value from 1.0 to 1.6) and made this simple expression:  Sum (Sales)/[FX RATE].  However, I need to apply this ONLY to values where Year = 2008,2009,2010 (or less than 2011).  How would I do this?  Attached is the example.  Thanks for the help.   

[erichshiino · Accepted solution]

Hi,

I'd prefer to create a table with at least year and the Fx rate.

If you prefer to kind of hardcode the rule, you can use set analysis to segment in two sums:

(Sum ( {<Year={"<2011"} >} Sales)/[FX RATE]) +

Sum ( {<Year={">=2011"} >} Sales)

The first part, divides the results before 2011 by Fx Rate.

The second part just sums the values since 2011

Hope it helps,

Erich

[#3]

Awesome, thanks Erich - works perfectly.