Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Syntax issues when referencing a field name

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!

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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 )

View solution in original post

7 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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 )

sunny_talwar

May be you need this:

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


or


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


or


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

Colin-Albert

I think you have missed some curly brackets.

Try

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

Not applicable
Author

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

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

Not applicable
Author

Tried exactly that and still have a syntax error.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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