turn IF statement into set analysis expression

[#1]

Hello,

How can I turn this simple if statement used in a chart table

=if(Currency <> 'EUR', Currency & ' Conversion Table' & '<url>http://www.x-rates.com/d/' & Currency & '/table.html',0)

into a statment that won't change no matter what is selected.

Right now only the selected or possible values have an effect on the table. Which leaves me with some empty url links.

I'm guessing set analysis is needed for this but I couldn't figure out a syntax to get the job done.

Thank you.

[sujeetsingh]

Hi,

Generally you can get this from set analysis as

=SUM({$<Currency=->Field}

[#3]

TRY THIS IN SET ANALYSIS

SUM({<Currency -= {'EUR'}>}  Currency   ) & ' Conversion Table' & '<url>http://www.x-rates.com/d/'  & '/table.html'

[#4]

Thank you vishwaranjan.

I've change the statement to:

=avg({1<Currency -= {'EUR'}>}  Currency   ) & Currency  & ' Conversion Table' & '<url>http://www.x-rates.com/d/'  & Currency & '/table.html'

but still I only get the selected values with the correct link.

I should add that I'm using the Currency as the dimension field. Could this be the problem, and how could I fix it?

[#5]

I adapted this from the "Data Visualization" Demo. In the sheet "Sparklines" you will find the "USD Exchange Rates"-Chart. If you add a "Currency" Listbox to the sheet and select one or more values you can see what happens - what I don't want to happen -> the url links break for the non-selected currencies.

[#6]

no ideas?

[#7]

Often, people set up a small table of Currency options as a "data island". That allows your document user to select a desired currency but the different currency types are not really associated with the rest of the data. Under those circumstances you cannot use set analysis to make your expression work.

Something that has worked for me is to make the desired currency identifier part of the conversion table. Then, when the document user selects, for example, Euro, the currency conversion factors for Euro automatically become the only available value and my expressions work fine.