Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Divide Field by Total of the Whole Column

TerritoryPotentialBPI
East500Potential / Total Potential or 500/1500
West1000
Total Potential1500

I want to take the Potential for each territory and divide it by the Potential of ALL Territories, but cannot get this to work.  I was trying set analysis (and we use Max(Year) in our app to accommodate the user selection).

I tried this:

(Sum({$<Year={$(=Max(Year))}>} [Sales Potential])) /  (Sum({$<Year={$(=Max(Year))}, [Territory]=>} [Sales Potential]))

But it doesn't work.  It appears to be ignoring the removal of the Territory in the formula as the calculation = 1 in every instance.  So, it's dividing Territory Potential by Territory Potential instead of Territory Potential / Total Potential.

Tags (1)
1 Solution

Accepted Solutions
jsaradhi
Valued Contributor

Re: Divide Field by Total of the Whole Column

so the first option should work. sum(Potential)/sum(TOTAL Potential)...here "TOTAL" is a key word.

test.png

10 Replies
jerem1234
Valued Contributor II

Re: Divide Field by Total of the Whole Column

Try something like:

sum(Potential)/sum(total Potential)

Hope this helps!

MVP
MVP

Re: Divide Field by Total of the Whole Column

SUM(Potential)/SUM(Total Potential)

Or

SUM(Potential)/SUM({1}Potential)

or

SUM(Potential)/SUM(All Potential)

jsaradhi
Valued Contributor

Re: Divide Field by Total of the Whole Column

assuming Total Potential is not an actual value in the Territory field, what happens if you do sum(Potential)/sum(Total Potential).

if Total Potential is an actual value in Territory field, you can use set analysis, something like:

sum(Potential)/sum({<Territory='Total Potential'>} Potential)

you can use your other set expressions (for max(Year), etc.) in both options:

sum(Potential)/sum({<Year={'max(Year)'} total Potential)

or

sum(Potential)/sum({<Territory={'Total Potential'}, Year={'max(Year)'}>} Potential)

Not applicable

Re: Divide Field by Total of the Whole Column

Thanks, but the Total Potential is just the total of the expression as calculated by QV. So, it’s not a distinct value.

There are multiple columns in this table and dividing by Total Potential seems to be unrecognized by QV. Total Potential is not really a distinct field.

Thanks,

Shelley

tobias_klett
Contributor II

Re: Divide Field by Total of the Whole Column

Hi,

you could also just check "Relative" for this Expression which is similar to sum(Total Value). It goes only by the total of the selction. To make it go by the total of all no matter what is selected you hav to use sum(all Value).

Check my demo and do selections.

Hope this helps.

Tobias

Highlighted
Partner
Partner

Re: Divide Field by Total of the Whole Column

HI,

You can use sum(Potential) \ sum( TOTAL Potential)

-Sathish

jsaradhi
Valued Contributor

Re: Divide Field by Total of the Whole Column

so the first option should work. sum(Potential)/sum(TOTAL Potential)...here "TOTAL" is a key word.

test.png

Not applicable

Re: Divide Field by Total of the Whole Column

Okay!  Thank you for pointing out that TOTAL is a key word.  I finally got it to work.  Thanks so much!

Not applicable

Re: Divide Field by Total of the Whole Column

THIS WORKS TOO AND IS VERY EASY!!!  THANKS FOR YOUR HELP!