17 Replies Latest reply: Jul 28, 2017 10:42 AM by Gabor Kubler

Correcting an expression error in set analysis

I'm working on data set from a book titled "QlikView Your Business" I have an error in the expression that I'm unable to fix and I'm looking for a little help.

sum ({\$<\$(filter_ThisYear)>}error message starts here. [# Amount] )/

sum ({\$<\$(filter_PriorYear)>} [# Amount] ) -1

This set analysis is working off of 2 variables

filter_ThisYear = Year={\$(=max(Year))}

filter_PriorYear = Year=[\$(=max(Year)-1}

troyansky

• Re: Correcting an expression error in set analysis

filter_PriorYear = Year={\$(=max(Year)-1}

• Re: Correcting an expression error in set analysis

filter_PriorYear = Year={\$(=max(Year)-1)}

• Re: Correcting an expression error in set analysis

Aar,

Thanks for the response I made some changes to the variables and the error message on Chart is was showing memory exceeded and is now showing "Too few expressions" .   When I go to the expression tab I have an error message "Errors in expression"  with the following formulas in play:

sum ({\$<\$(filter_ThisYear)>} [# Amount] )/
sum ({\$<
\$(filter_PriorYear)>} [# Amount] ) -1

• Re: Correcting an expression error in set analysis

Hi John,

if you post your whole document, I'll take a look at the variables, and the chart properties, to find our what might be wrong.

best,

Oleg Troyansky

• Re: Correcting an expression error in set analysis

Just an update to my question I've attached the qvw  I'm working on.    If you go into the expression in the chart properties there is an "expression error" message noted.    The expressions are creating the chart image but I'd like to be able to fix this as I had to type in all the elements of the formula vs using the Qlik auto fill on formulas.

troyansky

• Re: Correcting an expression error in set analysis

Replace below expression

sum({\$<Year={'<\$(=\$(filter_ThisYear))'>}[# Amount]) /

sum({\$<Year={'<\$(=\$(filter_ThisYear))'>}[# Amount]) -1

• Re: Correcting an expression error in set analysis

Surya, this won't work unfortunately. Not sure what did you have in mind, but this syntax is simply incorrect.

• Re: Correcting an expression error in set analysis

Hi Oleg,

Did you try implementing above?

I believe the idea is to assign a  calculated variable in set expression to restrict data based on Year field.

I have implemented above expression and it is working perfectly fine.

please elaborate on the error you are facing

• Re: Correcting an expression error in set analysis

I think what Oleg is trying to point out is that the variable is a set analysis syntax in itself...

so what you are suggesting is to embbed a set analysis into another set analysis.But the way it is going to be expanded, it is going to be the wrong syntax. Look here what happens when I use your expression in a straight table (converted from the chart)....

All nulls.... and this is because of how you are using this (replacing

sum({\$<Year={'<\$(=Year = {\$(=max(Year))})'>}[# Amount]) /

sum({\$<Year={'<\$(=Year={\$(=max(Year)-1)})'>}[# Amount]) -1

Coming back to the OPs question.... Why do you think your expression isn't working? Is it because you see the red wiggly lines? If that's what the concern is, then I would say you don't have to worry about anything because those red wiggly lines are just saying that your expression editor doesn't understand your syntax. This happens when you use set modifier embedded in a variable. Expression editor doesn't know what it will be expanded to and hence shows error. But to see what it is getting expanded to, I converted your chart to a straight table and removed the expression label to see this

If this was your goal, then you don't have to worry about anything and you are good to go. But in case this is not what you intended, please clarify what exactly are you looking to get

• Re: Correcting an expression error in set analysis

Sunny,

Thanks for the feedback, yes the red "wiggly" line was my concern so thanks for the feedback.

• Re: Correcting an expression error in set analysis

Hi Oleg,

I didnt notice the variable to be set expression.

filter_ThisYear = max(Year)

• Re: Correcting an expression error in set analysis

I get it I simply assumed we had

filter_ThisYear = max(Year)

• Re: Correcting an expression error in set analysis

if this was the case

filter_ThisYear = max(Year)

then below expression is workable right?@sunny talwar

sum({\$<Year={'<\$(=\$(filter_ThisYear))'>}[# Amount]) /

sum({\$<Year={'<\$(=\$(filter_ThisYear))'>}[# Amount]) -1

• Re: Correcting an expression error in set analysis

Yes, this or may be without dollar sign expansion

sum({\$<Year={'<\$(=filter_ThisYear)'>}[# Amount]) /

sum({\$<Year={'<\$(=filter_PriorYear)'>}[# Amount]) -1

• Re: Correcting an expression error in set analysis

filter_ThisYear = Year={\$(=max(Year))}

filter_PriorYear = Year=[\$(=max(Year)-1}

replace above variables with below

filter_ThisYear = max(Year)

filter_PriorYear = max(Year)-1

And use any of the below set of expressions.

with dollar sign expansion

sum({\$<Year={'<\$(=\$(filter_ThisYear))'>}[# Amount]) /

sum({\$<Year={'<\$(=\$(filter_ThisYear))'>}[# Amount]) -1

without dollar sign expansion

sum({\$<Year={'<\$(=filter_ThisYear)'>}[# Amount]) /

sum({\$<Year={'<\$(=filter_PriorYear)'>}[# Amount]) -1

• Re: Correcting an expression error in set analysis

Surya,

Thank you too.   I updated the filters & tried your first option w/ the dollar sign expansion, but unfortunately it didn't work.   I'm very new to Qlikview so I'm sure there maybe some things I'm overlooking w/ some of the other expressions that were part of the practice session I was working on.

• Re: Correcting an expression error in set analysis

Hi John,

I'm learning from the same book and had a similar issue to yours. In my case I had a typo in one of the expressions of the chart, but after spotting it everything worked. (and I was using the exact same expressions that the book provided so it should be OK).

I suggest double checking your expressions, and make sure you are enclosing everything and you don't have additional brackets or characters lying around in the expression. The hardest part here is what others have already mentioned, when you are using set expressions the expression editor goes mad with the red underlining.

Here are my expressions and variables:

Change in sales:

sum

({\$<\$(filter_ThisYear)>} [# Amount]) /

sum({\$<\$(filter_PriorYear)>} [# Amount]) -1

Change in Discount %

sum

({\$<\$(filter_ThisYear)>} [# Amount - Discounts]) /

sum({\$<\$(filter_ThisYear)>} [# Amount]) -

sum({\$<\$(filter_PriorYear)>} [# Amount - Discounts])/

sum({\$<\$(filter_PriorYear)>} [# Amount])

Current Year Sales

sum(

sum({\$<\$(filter_ThisYear)>} [# Amount])

filter_ThisYear:

Year={\$(=max(Year))}

filter_PriorYear:

Year={\$(=max(Year)-1)}