topic Re: Calculating percentage of total by dimension in App Development

Calculating percentage of total by dimension

remedibi — Fri, 14 Oct 2016 14:56:40 GMT

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.

Re: Calculating percentage of total by dimension

sunny_talwar — Fri, 14 Oct 2016 17:19:36 GMT

Try using the total qualifier in your denominator

Sum(Revenue)/Sum(TOTAL Revenue)

Re: Calculating percentage of total by dimension

remedibi — Thu, 20 Oct 2016 13:55:10 GMT

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!

Re: Calculating percentage of total by dimension

remedibi — Thu, 20 Oct 2016 15:16:44 GMT

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.

Re: Calculating percentage of total by dimension

sunny_talwar — Thu, 20 Oct 2016 16:44:20 GMT

Try this:

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

Re: Calculating percentage of total by dimension

sunny_talwar — Thu, 20 Oct 2016 16:44:51 GMT

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

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