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