Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

remedibi
New Contributor II

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
MVP

Re: Calculating percentage of total by dimension

Try using the total qualifier in your denominator

Sum(Revenue)/Sum(TOTAL Revenue)

remedibi
New Contributor II

Re: Calculating percentage of total by dimension

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
New Contributor II

Re: Calculating percentage of total by dimension

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

MVP
MVP

Re: Calculating percentage of total by dimension

Try this:

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

MVP
MVP

Re: Calculating percentage of total by dimension

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

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