Pivot Table total func problem

[berkarmagan]

Hi everyone,

I ve got a problem, i ve got a complicated data design which contains basicly Sales data and Visit data. I need to create a pivot table, region, country and products on dimension and also products on pivot side, like the example below. My problem is sales data contains product but visit data doesnt contain any products. What i need on visit expression is get count customers by country and region, but it is not up to products, just want to get visit count by region and country, products shouldnt affect the calculation. I mean the customer counts should be same on each products.

I create this expression COUNT({1} TOTAL<Region,Country>DISTINCT CustomerID) and I get what i want but when i select one of the product Visit calculate is changing, because product is just related with sales data, it is getting count customer on sales because of link. I put {1} TOTAL to expression for not effect from selections but it doesnt work.

Any suggest pls?

	Products	A		B		C
Region	Country	Sales	Visit	Sales	Visit	Sales	Visit
m	x	10	5	3	5	5	5
m	y	0	2	5	2	0	2
m	z	5	7	0	7	7	7
n	f	0	1	2	1	0	1
n	t	6	2	0	2	0	2

[sunny_talwar]

So selection in product changes this? I don't think it should, but would you be able to share a sample where it is changing?

[marcus_sommer]

Wouldn't be better to use a calculated dimension for the visits like:

aggr(count(CustomerID), Region,Country))

maybe with some additional {1} or TOTAL instead of creating an extra column for each product?

- Marcus

[berkarmagan]

My problem is i ve to use that calculation on other expression. If I use this on calculated dimension i got true answer but i can not use that calculated dimension in expression that s my problem.i.e. in excel file, for country x and product a; sales is 10 and visit count is 5 but i need to get on visit cell is 10/5 = 2. I mean i dont need to show that expression's result. If i can find anything to use calculated dimension in expression it solves my problem. But with my expression (COUNT({1} TOTALDISTINCT CustomerID)) which i used it s not working with my selections. Thanks. Berk.

[marcus_sommer]

I'm not sure if it's possible to solve it with an expression but maybe you need to extend your expression with P() and E() in set analysis expression.

- Marcus