topic Re: IF And Set Analysis in QlikView

IF And Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I'm having some very strange results using Set Analysis and IF

This statement works:

=only({< SIMULATE.Sim_Year = {$(=(Calendrier.Année))}>}SIMULATE.Sim_Rate)

When try to put an IF around it I'm getting crazy results which I cannot understand

=if(

Currency='Local' ,

only({< SIMULATE.Sim_Year = {$(=(Calendrier.Année))}>}SIMULATE.Sim_Rate)

)

Any help would be much appreciated Paul

There are two years - I need to get the rate from one of these selected years and apply this rate. The country linked to this SIM table as I sometimes I need the total for all countries (each having it's own rate)

Re: IF And Set Analysis

Sergey_Shuklin — Thu, 23 Aug 2018 12:39:06 GMT

Hello!

Try to get IF-condition into Only() function:

Only({<Set Analysis>} if(condition, true, false))

Re: IF And Set Analysis

Anonymous — Thu, 23 Aug 2018 12:45:02 GMT

No joy with this I'm afraid

=only({< SIMULATE.Sim_Year = {$(=(Calendrier.Année)) if(Currency='Local' ,SIMULATE.Sim_Rate,1) )

Re: IF And Set Analysis

sunny_talwar — Thu, 23 Aug 2018 12:48:46 GMT

How about this

=Only({<SIMULATE.Sim_Year = {$(=(Calendrier.Année))}, Currency = {'Local'}>}SIMULATE.Sim_Rate)

Re: IF And Set Analysis

Anonymous — Thu, 23 Aug 2018 13:00:33 GMT

Hi Sunny thanks for your answer. It's not working. I can see your logic.

What I'm looking for is

Give me a rate (for a year) then if the currency is 'LOCAL' use that rate, else divide by 1.

Hopefully this explains my requirement more.

Thanks,

Paul

Re: IF And Set Analysis

sunny_talwar — Thu, 23 Aug 2018 13:05:07 GMT

Would you be able to share a sample where you are doing this?

Re: IF And Set Analysis

Sergey_Shuklin — Thu, 23 Aug 2018 13:06:54 GMT

Are you sure that in your case Only() function is returning only one value per year? (SIMULATE.Sim_Rate)

Re: IF And Set Analysis

Anonymous — Thu, 23 Aug 2018 13:12:36 GMT

The Statement

=only({< SIMULATE.Sim_Year = {$(=(Calendrier.Année))}>}SIMULATE.Sim_Rate)

works so Yes I'm sure that there is only one year being returned

Adding example to original question

Re: IF And Set Analysis

sunny_talwar — Thu, 23 Aug 2018 13:22:22 GMT

Why do you think =only({< SIMULATE.Sim_Year = {$(=(Calendrier.Année))}>}SIMULATE.Sim_Rate) is working? All I see is null value for TEST Column where you seems to be testing this?

Re: IF And Set Analysis

Anonymous — Thu, 23 Aug 2018 13:36:40 GMT

Hi Sandy,

I think that this is working as

When I select country I get the rate for that year.

Re: IF And Set Analysis

sunny_talwar — Thu, 23 Aug 2018 14:24:14 GMT

This seems to work... part in red was missing

=only({< SIMULATE.Sim_Year = {$(=(Calendrier.Année))}>} if(Currency='Local' ,SIMULATE.Sim_Rate,1) )

Re: IF And Set Analysis

Anonymous — Thu, 23 Aug 2018 14:39:37 GMT

Hi Sunny, This syntax is driving me nuts!! Thanks so much for your help - it's really appreciated. Paul