Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 )
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 )
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)
I think you have missed some curly brackets.
Try
SUM({< CATEGORY_VALUE={'Name'} >} QUOTES) / SUM(TOTAL <CATEGORY> QUOTES)
I tried that (see below) and still have a syntax error.
(SUM(<[CATEGORY_VALUE]={'GAINWeb'}>QUOTES)/(SUM(TOTAL<CATEGORY>QUOTES)))
Tried exactly that and still have a syntax error.
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.
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