topic turn IF statement into set analysis expression in QlikView

turn IF statement into set analysis expression

Tue, 18 Jun 2013 06:29:42 GMT

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.

Re: turn IF statement into set analysis expression

sujeetsingh — Tue, 18 Jun 2013 06:36:00 GMT

Hi,

Generally you can get this from set analysis as

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

Re: turn IF statement into set analysis expression

Tue, 18 Jun 2013 06:49:24 GMT

TRY THIS IN SET ANALYSIS

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

Re: turn IF statement into set analysis expression

Tue, 18 Jun 2013 07:31:49 GMT

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?

Re: turn IF statement into set analysis expression

Tue, 18 Jun 2013 11:15:23 GMT

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.

Re: turn IF statement into set analysis expression

Wed, 19 Jun 2013 12:29:12 GMT

no ideas?

Re: turn IF statement into set analysis expression

Fri, 21 Jun 2013 19:57:25 GMT

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.