Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

turn IF statement into set analysis expression

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.

6 Replies
sujeetsingh
Honored Contributor III

Re: turn IF statement into set analysis expression

Hi,

Generally you can get this from set analysis as

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


Not applicable

Re: turn IF statement into set analysis expression

TRY THIS IN SET ANALYSIS

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

Not applicable

Re: turn IF statement into set analysis expression

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?

Not applicable

Re: turn IF statement into set analysis expression

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.

Not applicable

Re: turn IF statement into set analysis expression

no ideas?

Not applicable

Re: turn IF statement into set analysis expression

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.

Community Browser