Set Analysis - combing both field and variable evaluations in the same query

[#1]

Hi All,

Currently have two set analysis expressions that are working well independantly of each other (one working of a value found in the data, the second working off variables). 

I now want to combine both evaluations within the same formula:

sum({<MktWidthBuyer = { " =MktWidthBuyer<=MktWidthSeller " }>} MktQtyKG)

sum({<MktWidthBuyer = { ' <=$(varMktWidthMax)  >=$(varMktWidthMin) ' }>} MktQtyKG)

Thought I could just add the second expression as follows, but cannot get it to work correctly:

sum({<MktWidthBuyer = { " =MktWidthBuyer<=MktWidthSeller" '<=$(varMktWidthMax) >= $(varMktWidthMin)'  }>} MktQtyKG)

Any thoughts greatly appreciated...

Kind regards,

Rich

[swuehl]

Try

sum({<MktWidthBuyer = {"=MktWidthBuyer<=MktWidthSeller and MktWidthBuyer<=$(varMktWidthMax) and MktWidthBuyer>= $(varMktWidthMin)"}>} MktQtyKG)

Regards,

Stefan

[#3]

Hi Stefan,

Many thanks for your review, although at the moment it only returns a 0...

Have attached a qv doc as an example...

Any further thoughts, greatly appreciated

Kind regards,

Rich

[swuehl]

I noticed that your one expression with the WidthSeller comparison actually is checking against max(MktWidthSeller).

So if I change the expression to

sum({<MktWidthBuyer = {"=MktWidthBuyer<=max(MktWidthSeller) and MktWidthBuyer<=$(varMktWidthMax) and MktWidthBuyer>= $(varMktWidthMin)"}>} MktQtyKG)

I do get results if the two variables are set. Are these input values optional or can be assume at least some absolute min / max limits (so we can create additional variables that check that at least these limits are set. Or use constraints to the Input variables).

Regards,

Stefan

[#5]

Hi Stefan,

Yes, in fact it does work when both variables have been entered. 

In reality, these variables are optional - should the user want to focus on a specific width etc...  and therefore can have 0,1,2 values (ie an upper value & no lower value etc...)

Do you suggest to have  an if statement beforehand, working out which variables contain an input and then the relavant formula ie:

If len(varMktWidthMax)>0 and len(varMktWidthMin)>0,

sum({<MktWidthBuyer = {"=MktWidthBuyer<=max(MktWidthSeller) and MktWidthBuyer<=$(varMktWidthMax) and MktWidthBuyer>= $(varMktWidthMin)"}>} MktQtyKG)

if(len(varMktWidthMax>0 and len(varMktWidthMin) = 0,

sum({<MktWidthBuyer = {"=MktWidthBuyer<=max(MktWidthSeller) and MktWidthBuyer<=$(varMktWidthMax) "}>} MktQtyKG)

if(len(varMktWidthMax=0 and len(varMktWidthMin)>0,

sum({<MktWidthBuyer = {"=MktWidthBuyer<=max(MktWidthSeller) and MktWidthBuyer>= $(varMktWidthMin)"}>} MktQtyKG)

else

sum({<MktWidthBuyer = {"=MktWidthBuyer<=max(MktWidthSeller)"}>} MktQtyKG)

)))

Is there a tidier way of doing the above?

Kind regards,

Rich

[swuehl · Accepted solution]

Please check attached.

I created two new variables that check if the input variables have been set and if not, use some exorbitantly large limits (you must know that these limits will never be reached by your values).

Maybe like this?

Regards,

Stefan

[#7]

Hi Stefan,

Many thanks, your solution is perfect!!

Once again thanks for taking the time to review the attached,

Kind regards,

Rich