topic Re: if statement in set expression in QlikView

if statement in set expression

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

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'}

Re: if statement in set expression

Anil_Babu_Samineni — Wed, 18 Oct 2017 09:11:11 GMT

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)

Re: if statement in set expression

jonathandienst — Wed, 18 Oct 2017 09:25:36 GMT

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

Re: if statement in set expression

thomasreynaert — Wed, 18 Oct 2017 10:06:40 GMT

@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

Anil_Babu_Samineni — Wed, 18 Oct 2017 10:32:59 GMT

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

Re: if statement in set expression

thomasreynaert — Wed, 18 Oct 2017 15:16:24 GMT

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?

Re: if statement in set expression

lorenzoconforti — Wed, 18 Oct 2017 21:12:35 GMT

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

Re: if statement in set expression

jonathandienst — Thu, 19 Oct 2017 05:08:31 GMT

>>@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:

Build the expression in the simplest form (without any variables). Test.
Extend the expression withe the extra bells and whistles. Test thoroughly.
Identify what parts can be coded as variables (usually stuff that is common to many expressions) and develop and test the variables thoroughly
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

Re: if statement in set expression

thomasreynaert — Thu, 19 Oct 2017 10:59:49 GMT

Hi Lorenzo

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

Re: if statement in set expression

thomasreynaert — Thu, 19 Oct 2017 11:05:21 GMT

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.

Re: if statement in set expression

lorenzoconforti — Thu, 19 Oct 2017 11:28:31 GMT

Attached

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

Lorenzo

Re: if statement in set expression

thomasreynaert — Thu, 19 Oct 2017 12:07:41 GMT

Thank you! I was making adaptions in the variable with ' & " but nothing seemed to work there. So I wouldn't come up with the idea of changing the 'source' variables.

But it works, very happy with that!

Re: if statement in set expression

lorenzoconforti — Thu, 19 Oct 2017 12:12:13 GMT

No problem. You just need to think of how those variables are evaluated in the chain. I usually work on them step by step with text boxes