topic Re: Set Analysis - using FieldValue function in QlikView

Set Analysis - using FieldValue function

Tue, 15 Jan 2013 11:22:40 GMT

This formula works:

sum({<MEASURE_DESC = {'Profit Growth'}>} KPI)

But, if I try this formula it does not. Can you use the FieldValue function in set analysis?

sum({<MEASURE_DESC = {"=Fieldvalue('MEASURE_DESC',1)"}>} KPI)

Thanks

Re: Set Analysis - using FieldValue function

swuehl — Tue, 15 Jan 2013 11:34:38 GMT

Try

sum({<MEASURE_DESC = {"$(=Fieldvalue('MEASURE_DESC',1))"}>} KPI)

Re: Set Analysis - using FieldValue function

Tue, 15 Jan 2013 11:45:14 GMT

Hi Stefan

It works, thankyou, but it shows the formula as an error?

Re: Set Analysis - using FieldValue function

swuehl — Tue, 15 Jan 2013 11:49:11 GMT

The syntax checker probably gets confused by the multiple single quotes (note that I change my expression to use double and single quotes, while single quotes only were my first approach and could / should work, too).

You can also create a variable:

vMeasureDesc

=Fieldvalue('MEASURE_DESC',1)

and use the variable in the expression:

sum({<MEASURE_DESC = {'$(vMeasureDesc)'}>} KPI)

Re: Set Analysis - using FieldValue function

Tue, 15 Jan 2013 11:54:11 GMT

for my own understanding, why would the formula only work if you include the $ expansion?

Re: Set Analysis - using FieldValue function

jonathandienst — Tue, 15 Jan 2013 12:14:19 GMT

The $(= tells QV to evaluate the expression, and match the result of the evaluation. Otherwise, it is trying match the string "=Fieldvalue('MEASURE_DESC',1)" (not evaluated).

Regards

Jonathan

Re: Set Analysis - using FieldValue function

swuehl — Tue, 15 Jan 2013 12:19:58 GMT

You need a dollar sign expansion if you want to dynamically retrieve a value (literal) for your field value list in your set modifier.

If you want to use an advanced search (like, in your OP, using {"=expression"} ), I think you can write it like this:

=sum({<MEASURE_DESC = {"=MEASURE_DESC=fieldvalue('MEASURE_DESC',1)"}>} KPI)

which is an advanced search using a record based comparison. I would prefer using the dollar sign expansion variant.

Hope this helps,

Stefan

Re: Set Analysis - using FieldValue function

Anonymous — Mon, 20 Aug 2018 07:25:30 GMT

Hi Stefan,

Would the row based search work in Qlik Sense too ? I am trying to use a set analysis to get the sum of a measure value based on a date field. This date field may be different for each row in the table, so can the set analysis work in this case? Right now, I am just getting the sum of the measure value for only one of the dates and it appears 0 for the rest of the dates even though there is value available for them.

Note: The date field is actually derived from a variable and varies for each row.