if statement in set expression

[thomasreynaert]

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

[Anil_Babu_Samineni]

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)

[jonathandienst]

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

[thomasreynaert]

@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

[Anil_Babu_Samineni]

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

[thomasreynaert]

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]

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

[jonathandienst]

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

[thomasreynaert]

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]

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.