Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Set Analysis - using FieldValue function

Hi

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis - using FieldValue function

Try

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

7 Replies
MVP
MVP

Re: Set Analysis - using FieldValue function

Try

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

Not applicable

Re: Set Analysis - using FieldValue function

Hi Stefan

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

Capture1.PNG

MVP
MVP

Re: Set Analysis - using FieldValue function

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)

Not applicable

Re: Set Analysis - using FieldValue function

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

MVP
MVP

Re: Set Analysis - using FieldValue function

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Set Analysis - using FieldValue function

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

rimaghosh
New Contributor

Re: Set Analysis - using FieldValue function

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.