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: 
remedibi
Contributor III
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
sunny_talwar

Try using the total qualifier in your denominator

Sum(Revenue)/Sum(TOTAL Revenue)

remedibi
Contributor III
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!

remedibi
Contributor III
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.

Capture.PNG

sunny_talwar

Try this:

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

sunny_talwar

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

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