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.)
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.