Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
thomasreynaert
New Contributor III

if statement in set expression

Hi everyone

Could someone explain so I understand QV logic better why this is:

Not a working formula

Count({$<[so_order_date_YYYYMM]={">=$(=min(date($(vPeriod3),'YYYYMM')))<=$(=max(date($(vPeriod4),'YYYYMM')))"},so_order_date_month={$(vExp_Selected_Date_Month)},$(vExp_Unselect_datefields),

If($(vShowUSLogic)=0,$(vStock),$(vStockUS)),$(vExternalClients),$(vExcludingArticleTypes)>}  so_ordernumber)

and this:

Working formula

=If($(vShowUSLogic)=0,Count({$<[so_order_date_YYYYMM]={">=$(=min(date($(vPeriod3),'YYYYMM')))<=$(=max(date($(vPeriod4),'YYYYMM')))"},so_order_date_month{$(vExp_Selected_Date_Month)},$(vExp_Unselect_datefields),

$(vStock),$(vExternalClients),$(vExcludingArticleTypes)>}  so_ordernumber) ,

Count({$<[so_order_date_YYYYMM]={">=$(=min(date($(vPeriod3),'YYYYMM')))<=$(=max(date($(vPeriod4),'YYYYMM')))"},so_order_date_month={$(vExp_Selected_Date_Month)},$(vExp_Unselect_datefields),

$(vStockUS),$(vExternalClients),$(vExcludingArticleTypes)>}  so_ordernumber))


vStock & vStockUS are just variables containing a selection: delivertime_desc={'A','B','C'}

1 Solution

Accepted Solutions
lorenzoconforti
Contributor III

Re: if statement in set expression

Attached

I've modified your variables so now only the box on top right works but that's expected

Lorenzo

View solution in original post

12 Replies

Re: if statement in set expression

You can initiate highlighted one is vShowUSLogic??

Count({$<[so_order_date_YYYYMM]={">=$(=min(date($(vPeriod3),'YYYYMM')))<=$(=max(date($(vPeriod4),'YYYYMM')))"},so_order_date_month={$(vExp_Selected_Date_Month)},$(vExp_Unselect_datefields),

If($(vShowUSLogic)=0,$(vStock),$(vStockUS)),$(vExternalClients),$(vExcludingArticleTypes)>}  so_ordernumber)



Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP
MVP

Re: if statement in set expression

Not working how? Incorrect numbers or zero or error?

Its really hard to debug an expression like this using so many variables, any one of which may or may not be contributing to the problem. The correct way to debug and fix this is to replace all the variables with the expected values, and remove selections and other elements that are not part of the problem. When its working, then re-insert the variables one at time to confirm that the variables are working as expected.

That said, Count(...If()) and If(Count(...)) are logically different operations.

     Count(...If()) will iterate over the value and include only those that match the If() condition,

     If(,,,(Count()) will only evaluate (over all values) if the condition is met, otherwise it will be null

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
thomasreynaert
New Contributor III

Re: if statement in set expression

@Jonathan: well it will gives me no value.

I did as suggested until I removed all variables.Still I have the same result:

Does not work

Count({$<If($(vShowUSLogic)=0,$(vStock),$(vStockUS))>} so_ordernumber)

Works:

If($(vShowUSLogic)=0,Count({$<$(vStock)>} so_ordernumber),Count({$<$(vStockUS)>} so_ordernumber))

FYI vShowUSLogic=if(LOG_Company='USCompany' and $(vUSLogicActive)=1 and Count(DISTINCT so_branch)>1,1,0)

vUSLogicActive is just a button that users can toggle so it will give 0 or 1

Re: if statement in set expression

When the variable is just Toggle This won't work as expected. Because Left side condition won't work in Set analysis.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
thomasreynaert
New Contributor III

Re: if statement in set expression

So even if the variable works perfectly, should I avoid using it in the set expression?

Also to the point of Jonathan:

That said, Count(...If()) and If(Count(...)) are logically different operations.

What can I do, to get the result I want?

lorenzoconforti
Contributor III

Re: if statement in set expression

Why don't you just put this "=If($(vShowUSLogic)=0,$(vStock),$(vStockUS))" into a separate variable that you then call in your set expression?

MVP
MVP

Re: if statement in set expression

>>@Jonathan: well it will gives me no value.

Variables work great in set expressions - I use them all the time. But I develop complex expressions like this:

  1. Build the expression in the simplest form (without any variables). Test.
  2. Extend the expression withe the extra bells and whistles. Test thoroughly.
  3. Identify what parts can be coded as variables (usually stuff that is common to many expressions) and develop and test the variables thoroughly
  4. Substitute the variables into the expression and test again to make sure that they are working correctly.

And no, you did not remove the variables,

Count({$<If($(vShowUSLogic)=0,$(vStock),$(vStockUS))>} so_ordernumber)


And

Does not work

gives no information about how to fix  your expressions

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
thomasreynaert
New Contributor III

Re: if statement in set expression

Hi Lorenzo

That really seems a good idea. However my formula would not calculate. It seems like it is impossible to replace the variable.

thomasreynaert
New Contributor III

Re: if statement in set expression

Hi all

Thanks for all your patience so far!

I really downsized the problem to a ridiculously simple example attached.

As you can see, both variables ( true and false variable) work, as does the condition variable. In the example attached you can see '53' or '73' as values. Basically I want to be able to switch between the 2.

Please note that ,referring to my original post, simply changing the formula to

=If($(vShowCondition)=0,Sum({$<$(vRepSel1)>}Amount),Sum({$<$(vRepSel2)>}Amount)) is not what I am looking for.