topic Re: Set Analysis - combing both field and variable evaluations in the same query in QlikView

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

Tue, 21 Feb 2012 14:59:39 GMT

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

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

swuehl — Tue, 21 Feb 2012 16:28:27 GMT

Try

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

Regards,

Stefan

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

Tue, 21 Feb 2012 17:09:22 GMT

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

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

swuehl — Tue, 21 Feb 2012 17:26:49 GMT

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

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

Tue, 21 Feb 2012 17:39:26 GMT

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

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

swuehl — Tue, 21 Feb 2012 17:55:09 GMT

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

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

Tue, 21 Feb 2012 18:02:16 GMT

Hi Stefan,

Many thanks, your solution is perfect!!

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

Kind regards,

Rich