Discussion board where members can get started with QlikView.
Below are my input and Output tables.. Please suggest the best way to achieve this -
- Region and Sub-region are taken as dimensions
- Chart type - pivot table
- %sales contribution formula (for china let's say) = 123/(123+521+214), in other words - Sum of sales of sub-region / Total sales of the respective region
Thanks in advance
Trust that you are doing great!
To fulfill your requirement follow below given steps:
1. Create new chart as Pivot table (Title - Net Sales).
2. Add Region & Sub-Region dimensions from Available Fields/Groups to Used Dimensions.
3. Next, add Net Sales expression i.e. Sum(Sales)
4. Post that add, % Sales Contribution/Region expression definition as below:
Sum(Sales) / Sum(TOTAL <Region> Sales)
Sum(Sales) - will give total sales for that Region and Sub-Region
Sum(TOTAL <Region> Sales) - will generate Region level Sales total
5. For % Sales Contribution/Region show in Percent (%) settings under Number tab.
Please refer sample application attached herewith.
Thanks Deva! In Fact, I was also using the same expression. Do not know why it gets null values at some places.
Vineeth's solution works perfectly though.