topic Re: Syntax issues when referencing a field name in QlikView

Syntax issues when referencing a field name

Tue, 01 Mar 2016 14:21:49 GMT

I have a pivot table in Qlik and I'm trying to add a new expression that looks at the sum of quotes where category_value = 'Name' (a field specified) divided by sum of total quotes at the category level.

I'm having trouble getting the syntax correct.

(SUM(<[CATEGORY_VALUE]='Name'>QUOTES)/(SUM(TOTAL<CATEGORY>QUOTES)))

I've tried different variations - adding in { } and rearranging [ ] and < > to no avail.

CATEGORY_VALUE and CATEGORY are both dimensions in the pivot table, and Quotes is a column expression. If I take out the ='NAME' portion of the code, it is correct, which makes me think I'm doing something wrong when referencing the field value itself.

Please help!

Re: Syntax issues when referencing a field name

Peter_Cammaert — Tue, 01 Mar 2016 17:30:55 GMT

The 'Name' string part should be between curly braces, like {'Name'}

And the entire set analysis specification should be put between braces as well. Try with:

( SUM({<[CATEGORY_VALUE]={'Name'}>} QUOTES)/(SUM(TOTAL <CATEGORY> QUOTES)))

(Yes indeed, a lot of punctuation marks )

Re: Syntax issues when referencing a field name

sunny_talwar — Tue, 01 Mar 2016 17:33:18 GMT

May be you need this:

Sum({<CATEGORY_VALUE = {'Name'}>} QUOTES)/Sum(QUOTES)

Sum({<CATEGORY_VALUE = {'Name'}>} QUOTES)/Sum(TOTAL <CATEGORY_VALUE > QUOTES)

Sum(QUOTES)/Sum(TOTAL <CATEGORY_VALUE > QUOTES)

Re: Syntax issues when referencing a field name

Colin-Albert — Tue, 01 Mar 2016 17:33:51 GMT

I think you have missed some curly brackets.

Try

SUM({< CATEGORY_VALUE={'Name'} >} QUOTES) / SUM(TOTAL <CATEGORY> QUOTES)

Re: Syntax issues when referencing a field name

Tue, 01 Mar 2016 17:35:08 GMT

I tried that (see below) and still have a syntax error.

(SUM(<[CATEGORY_VALUE]={'GAINWeb'}>QUOTES)/(SUM(TOTAL<CATEGORY>QUOTES)))

Re: Syntax issues when referencing a field name

Tue, 01 Mar 2016 17:37:55 GMT

Tried exactly that and still have a syntax error.

Re: Syntax issues when referencing a field name

Peter_Cammaert — Tue, 01 Mar 2016 20:06:05 GMT

The set analysis specification in the first SUM() call should be surrounded by curly braces too. Like:

SUM( { <[CATEGORY_VALUE] = {'GAINWeb'}> } QUOTES) ...

To make it easier on you, you could use more whitespace to group the important parts of an expression. And don't be mistaken by the layout of the second SUM() call. That one doesn't use set analysis but a TOTAL modifier with a field list between angled brackets.

Re: Syntax issues when referencing a field name

Peter_Cammaert — Tue, 01 Mar 2016 20:13:36 GMT

BTW the red wiggly lines underneath an error in the expression editor do start where something is missing or not properly understood by the syntax checker. It will help you find the thing that is missing.

If you think there is something wrong with the balancing of parentheses and such (for example, in a complex expression with lots of nested IF() functions), move the cursor immediately in front of the opening bracket. That one and the matching closing bracket will be underlined (if possible). You can easily find out If some are missing.

Best,

Peter