% of overall total

[#1]

Hello -

I'm struggling a bit with a straight table chart. I have the following data set:

Region	Sales Amount
EMEA	100
APAC	200
AMER	500
Total	800

I have a list box for users to select regions. So, if a user selects EMEA and APAC, I want to show the following table:

Region	Sales Amount	Sales Contribution
EMEA	100	.125 (100/800)
APAC	200	.25 (200/800)

I tried the following formula for sales contribution: sum({$} salesamount) / sum( {1} salesamount ); however, it always returns 100%. I understand that the constant "1" should return all records in the application, but I think that by having the region dimension in the table the data is still being sliced.

Any help is appreciated!

Thanks

Region Name, Sales Amount, Sales Contribution

[johnw · Accepted solution]

I suspect you're right that the problem is it is still slicing by the Region dimension. The "total" key word should get around that. Does this work?

sum(salesamount)/sum({1} total salesamount)

[hector]

I, and if you use the old fashioned way?

Sum(SalesAmount) / Sum(all SalesAmount)

C ya

[#4]

Thanks to both you for the quick response! I just got started with Qlikview today and there is some awesome functionality...just gotta pick up the small things like this.

Thanks again!

[johnw]

That works too, but QlikTech appears to be trying to phase out the "all" keyword. In version 9, it will produce the right results, but it gives the error "Bad field names(s): all" and flags it in red. The pop up help for sum in version 9 is "num Sum([{SET}][DISTINCT][TOTAL [<Fields>]] expr)", so no longer includes "all". Finally, and more conclusively, the reference manual has this to say:

"In previous QlikView versions, the all qualifier may occur before an expression. This is equivalent to using {1} total, i.e. in such a case the calculation will be made over all the values of the field in the document, disregarding the chart dimensions and current selections. (The same value is always returned regardless of the logical state of the document.) If the all qualifier is used, a set expression cannot be used, since the all qualifier defines a set by itself. For legacy reasons, the all qualifier will still work in this QlikView version, but may be removed in coming versions. " (emphasis added)

Based on these stern warnings, I've stopped using "all", and now only use "{1} total", even though both still work.