Pivot - Calculated dimensions - Calculate customer buying in two shops

[#1]

Dear community,

Please, may you help me on a tricky chart? Imagine this situation:

I have some shops and lots of customers. Customers may buy in more than one shop.

ShopA   Customer1    1000$
ShopA   Customer2    900$
ShopA   Customer2    300$
ShopB   Customer1    1100$
ShopB   Customer3    1200$
ShopC   Customer1    1000$
ShopC   Customer2    500$
ShopC   Customer3    700$
ShopC   Customer3    100$

What I like to do is to calculate the number of distinct customer who bought something in both "ShopA and ShopB", "ShopA and ShopC", ... and so on.
The output should be something like:

ShopA ShopA 2
ShopA ShopB 1
ShopA ShopC 2
ShopB ShopA 1
ShopB ShopB 2
ShopB ShopC 2
ShopC ShopA 2
ShopC ShopB 2
ShopC ShopC 3

I tried creating a calculated dimension baseg on 'aggr' function.
This means that my pivot would have got 2 dimensions:

- Shops
- aggr(shops, customers)

And a single expression:

- Count (DISTINCT customers)

But this is the result:

ShopA ShopA 2
ShopB ShopB 2
ShopC ShopC 3

I did lots of other test, but none of them seems to work.
Have you any idea on how to implement it?

Thanks in advance
Fabio

[sbaldwin]

Hi i would do this in the load script, load your table then load it again but with the shop renamed to shop2, keeping customers the same so you can then add the shop and shop2 to a chart and do a distinct count of the customer i have attached the example.

Thanks

Steve

[#3]

Hi Steve, thanks for you answer!

Your idea is very useful, but unluckily it doesn't solve completely my problem.
Implementing it in the dataload script, the chart will ignore all the other selection that a user may set using the application.

Unluckily my example was too semplified (my fault): in my application I have 25 dimensions.

In example: year, product line, month ... etc ...

If I load a second table in dataload script containing only "customer" and "shop", the final count will ignore all the selection the user sets browsing my report.

Obviously I may add to the second table all the dimension I need to preserve ... but this will duplicate the number of data in the application (I tried it and the application became very slow).

Isn't there a way to manage this using set analysis and/or calculated dimensions?

Thanks in advance!

Fabio