IF statement in Set Expression

[Qlik_vic]

Hi,

Is it possible to use IF statements in Set Variables?

If would like to do the following:

vSetExcludeItems:

If (year>=2018, ProductCode-={'44636'}, ProductCode-={'46488'})

But it doesnt work, so am I write it in the wrong way or is it not possible to do like this?

So I want to be able to use the vSetExcludeItems in charts and tables in a set expression, so that I do not have to do an if statement in all chart objects.

[sunny_talwar]

This won't work like this, but may be something like this

{<year *= {">=2018"}, ProductCode -= {'44636'}>+<year *= {"<2018"}, ProductCode -= {'46488'}>}

[Qlik_vic]

Unfortunately that didnt work either

[sunny_talwar]

Would you be able to provide a sample where you are trying this?

[David_Capan]

Try making a flag in the script:
if( year >= 2018, if(ProductCode = '44636', 0,1), if(ProductCode = '46488', 0,1)) as Product_Flag
Then in your set analysis you could use {<Product_Flag = {1}>}

[Qlik_vic]

The fields are in separate tables, how should I do when the year comes from the common calendar and the product from product table?

[David_Capan]

The lookup function may work
if(Lookup('year','Date_Key',Date_Key,'CalendarTable') >= 2018, if(ProductCode = '44636', 0,1), if(ProductCode = '46488', 0,1)) as Product_Flag
Then in your set analysis you could use {<Product_Flag = {1}>}

Note:  You may have to perform a RESIDENT load to do this, assuming you're loading the calendar at the end of the script, as you can only look up values that have already been loaded.

[Qlik_vic]

Hi,

I cannot get it to work or then I just end up with loops in the tables. I can just make the flags in separate tables so I have one product flag and one year flag then of course I can make an union in the set expression, but then the problem is there are charts that need to have set variables as well in the expressions. Is there any way to combine more than two flags or flags and set variables?

[PrashantSangle]

how did you implement sunny's solutions . I think it should work.

1(using sunny solution):
Sum({<year = {">=2018"}, ProductCode -= {'44636'}>+<year *= {"<2018"}, ProductCode -= {'46488'}>}Sales)

2:If (year>=2018,Sum({<ProductCode-={'44636'}>}Sales), Sum({<ProductCode-={'46488'}>}Sales))

[Qlik_vic]

Hi,

The solution Sunny provided gives the correct result for 2018, but for 2017 it shows both products.