topic Re: Ignore Dimension in calculation Pivot table in QlikView

Ignore Dimension in calculation Pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Want to calculate market share in Pivot table for the below data,

In pivot table I have done cross tabulation with brand and below that I have market share, I want to get sum of brands in calculation ignoring the cross tabulation in Pivot table.

	Brand 1	Brand 2	Brand 3	Brand 4
	Market Share	Market Share	Market Share	Market Share
Region 1	65	97	93	96
Region 2	69	80	69	54
Region 3	67	92	69	59
Region 4	93	68	81	88
Region 5	93	85	87	92
Region 6	82	89	91	61

Re: Ignore Dimension in calculation Pivot table

swuehl — Thu, 16 Jun 2016 09:57:40 GMT

If you want to ignore the dimensions in a table chart, you can use the TOTAL qualifier, also possible adding a field list to only consider specific dimensions:

Sum( Amount)

will consider all dimensions

Sum(TOTAL Amount)

will consider no dimensions (evaluation in total context)

Sum(TOTAL<Region> Amount)

will consider only Region dimension

The Aggregation Scope

Re: Ignore Dimension in calculation Pivot table

Thu, 16 Jun 2016 10:25:06 GMT

Does not solve my problem.

Output should be

(Region 1, Brand 1) that is 65 divided by sum of (Region1 and Brand 1,2,....) that is 65+97+93+96

65 divided by 351

Re: Ignore Dimension in calculation Pivot table

sunny_talwar — Thu, 16 Jun 2016 10:27:21 GMT

May be like this (assuming Sum(Sales) is your original expression):

Sum(Sales)/Sum(TOTAL <Region> Sales)

Re: Ignore Dimension in calculation Pivot table

swuehl — Thu, 16 Jun 2016 10:33:33 GMT

Have you tried applying the principles of the TOTAL qualifier to your setting?

What about an expression like

=Sum(TOTAL<Region> Amount)

(Replace Amount with the field you are using in your current expression).

Does it return 351 for Region1 in each column?

If not,please post a complete description of your chart, all dimensions used, all expressions used.

Re: Ignore Dimension in calculation Pivot table

sunny_talwar — Thu, 16 Jun 2016 10:53:45 GMT

Here is a sample attached

Clearly shows the expression which Stefan has tried to explain you. See if this is what you want or not