topic Re: Variable in Set Analysis in QlikView

Variable in Set Analysis

cbaqir — Thu, 18 Feb 2016 23:04:30 GMT

Why would this expression give me Error in set modifier ah doc element list?

=SUM({<FCST_DATE={'$(vPriorQuarter)'},FCST_AMT)

Re: Variable in Set Analysis

Peter_Cammaert — Thu, 18 Feb 2016 23:09:04 GMT

Maybe because part of the set analysis is missing? Like a terminating >}. And the comma doesn't belong there.

Try with:

=SUM({<FCST_DATE={'$(vPriorQuarter)'}>} FCST_AMT)

Re: Variable in Set Analysis

cbaqir — Thu, 18 Feb 2016 23:13:57 GMT

Whoops. Thanks. Same error though.

=Sum({$<FCST_DATE={'$(vPriorQuarter)'}>}FCST_AMT)

Re: Variable in Set Analysis

sunny_talwar — Thu, 18 Feb 2016 23:20:12 GMT

What does your vPriorQuarter expand to in a text box object?

Re: Variable in Set Analysis

Peter_Cammaert — Thu, 18 Feb 2016 23:23:48 GMT

Into some value that includes quotes?

Re: Variable in Set Analysis

Oleg_Troyansky — Thu, 18 Feb 2016 23:24:29 GMT

Hi Cassandra,

this is hard to guess, without seeing your application. A few thoughts:

- Expression Editor doesn't recognize variables in Set Analysis expressions, so it always shows "Error in Expression", even for valid expressions.

- What's in the variable vPriorQuarter - is that a date? or a Quarter?

- If you use this expression in a Straight Table with an empty label, what formula do you see in the heading? You should be able to see how the variable gets expanded, and that might show you what could be wrong.

Cheers.

Come back to study our new and improved curriculum at the Masters Summit for Qlik - coming soon to Milan, Italy, and Austin, TX!

Re: Variable in Set Analysis

Peter_Cammaert — Thu, 18 Feb 2016 23:25:26 GMT

BTW in case of an error, the expression editor will use red "wiggly" lines to mark the expression part that is in error. what part would that be?

Re: Variable in Set Analysis

Peter_Cammaert — Thu, 18 Feb 2016 23:30:57 GMT

Oleg,

- Expression Editor doesn't recognize variables in Set Analysis expressions, so it always shows "Error in Expression", even for valid expressions.

Does that include $-sign substitution between quotes?

Peter

Re: Variable in Set Analysis

swuehl — Thu, 18 Feb 2016 23:45:40 GMT

Just guessing:

your variable is defined without a leading equal sign, hence expand to the expression itself, not the evaluated value.

If so, try adding a leading equal sign to your variable definition, or add an equal sign to the dollar sign expansion:

=SUM({<FCST_DATE={'$(=vPriorQuarter)'}>} FCST_AMT)

[I would prefer changing the variable definition]

If that's not the case / cause, please post your definition of the variable.

Re: Variable in Set Analysis

cbaqir — Fri, 19 Feb 2016 14:05:29 GMT

09/30/2015

Re: Variable in Set Analysis

cbaqir — Fri, 19 Feb 2016 15:04:00 GMT

vPriorQuarter is a single end date of the latest quarter. That is what I want to use to identify the row of the FCST_AMT.

='vPriorQuarter = '& $(vPriorQuarter) produces 09-30-2015

"If you use this expression in a Straight Table with an empty label, what formula do you see in the heading? You should be able to see how the variable gets expanded, and that might show you what could be wrong."

I see 09-30-2015

Re: Variable in Set Analysis

sunny_talwar — Fri, 19 Feb 2016 15:08:20 GMT

I think you should probably add another dollar sign expansion -> $(=$(vPriorQuarter)), and secondly make sure that the date format for FCST_AMT is MM-DD-YYYY.

Try this may be:

=Sum({$<FCST_DATE={'$(=$(vPriorQuarter))'}>}FCST_AMT)

Re: Variable in Set Analysis

cbaqir — Fri, 19 Feb 2016 15:11:24 GMT

That was it! Thanks! I'll get the hang of this syntax one day!

Re: Variable in Set Analysis

swuehl — Fri, 19 Feb 2016 15:18:47 GMT

So my guess was right ?!