# App Development

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for
Did you mean:
Contributor III

## Calculating percentage of total by dimension

I'm trying to figure out if I can do something in Qlik or if I have to do it at the SQL Server Database level via SQL code.

Basically, I'm making a pivot table in Qlik Sense that shows revenue by month for some dimensions. What I'm trying to do, is create a % of Total Revenue column that will take the revenue for the dimension and then compare it against the total revenue for all dimensions.

So for instance, if I have...

Colors  | Revenue | % of Total Rev

Blue     |  \$10         |       10%

Red     |  \$20         |        20%

Green  |  \$70         |        70%

I would want to see 10% total revenue next to blue, 20% total revenue next to red, and then 70% total revenue next to green.

That is over simplifying it, but it's essentially the formula I'm trying to come up with. Comparing the total revenue of one slice of a dimension against the cumulative total of results under the same dimension (colors in the instance above.)

Thank you.

5 Replies
MVP

Try using the total qualifier in your denominator

Sum(Revenue)/Sum(TOTAL Revenue)

Contributor III
Author

This looks like it could work, but I'm having trouble making it work in a pivot table. I made a pivot table that is broken out as such...

Rows: City, Month

Columns: Brand/Generic (drugs)

Measures: Sum Revenue, Rev %

The revenue % is what I'm trying to get to work within the pivot table. I need it to give me the percentage revenue of the brand total vs. the total brand vs. generic by city and month.

I can do this via the SQL, but I'm hoping to be able to do this in Qlik to make it easier to scale.

Thanks for the help with this!

Contributor III
Author

This might help explain it better. Check out the screenshot. I need that rev % number to be the percentage of 1,305 / (1305 + 1212). I have a line for every month, by city as well. This is just one line, breaking out brand vs. generic for one city for one month. I need this done on each line but I can't figure out how to format the formula to handle this. it has to be by both city and month.

MVP

Try this:

Sum(Revenue)/Sum(TOTAL<City, Month> Revenue)

MVP

Or may be just this (Can't see the complete image, so not 100% sure)

Sum(Revenue)/Sum(TOTAL <Month> Revenue)

Tags
Community Browser