Pivot table set analysis - Ignoring dimension under specific condition

[#1]

Hi,

I have an issue - not sure if it's set analysis or something else that's gonna solve the issue.

Lets say my data set is just one table with these columns:

Company

Quarter

ProductID

DateSold

I can see how many different types of products were sold by each company in a straight table like this:

Company	Types Sold
QrikView	10
ABM	18

The expression would be =count(distinct ProductID).

Here comes the problem: I want to create a pivot table like this:

Company	Quarter	Types Sold	Totals Sold	%
QrikView	Q1	1	22	5%
	Q2	2	22	9%
	Q3	3	22	14%
	Q4	4	22	18%
ABM	Q1	3	22	14%
	Q2	4	22	18%
	Q3	5	22	23%
	Q4	6	22	27%

How do I get the totals to show up the same as the straight table above? Basically I want that column to ignore the Quarter dimension so that I get 10 for QrikView and 18 for ABM. (1+2+3+4=10,    3+4+5+6=18).

Types Sold would have the same expression. Count(Distinct ProductID).

But to get the Total Sold I can't get what I'm getting in the straight table.

The Totals Sold column above is using this expression: Count({<Quarter=>} total ProductID). I've tried various things that don't seem to work.

I've attached sample QVW.

Thank you for reading this.

[jvitantonio · Accepted solution]

Here you go.

[#3]

Gracias