Set Analysis - using values of a dimension

[#1]

Hello,

Is there a way to incorporate values - of a dimension of the report - using set analysis without

calling them out explicitly?

Background:

Let's suppose that we have 2 different "Store" fields - (1) Customer Store & (2) Transaction Store -

in the dashboard. They contain more or less the same set of values, yet they serve different

purposes.

Customer Store is the default store for each customer, as defined in the customer master;

Transaction Store is the store listed on an actual transaction line, so if a customer has shopped at more than 1 location/store, there

would be multiple Transaction Stores for that customer.  Customer Store is in the customer table, Transaction Store is in the transaction details table.

These two tables are linked by the customer ID/code.

Problem:

We can only use "Transaction Store" as the dimension of a straight table, reason being that sales, cost, margin, etc need to be analyzed by the stores

in which the transactions actually occurred.  However, in this same report, we also want to capture various customer retention statistics by looking at the

"Customer Store".

We know that we can call out the values explicitly in set analysis, as in the example below:

count( {1<CustomerStore = {“Store A”}>} NewCustomer )

But to go a step further, is it possible to make this comparison dynamic?  For each line of the report, we would like

the calculation look at the value of the TransactionStore and grab the corresponding CustomerStore.

The resulting expression would essentially display identical results as the following individual calculations:

Row 1: count( {1<CustomerStore = {“Store A”}>} NewCustomer )

Row 2: count( {1<CustomerStore = {“Store B”}>} NewCustomer )

Row 3: count( {1<CustomerStore = {“Store C”}>} NewCustomer )

etc...

Any ideas? Thank you!

-Stephen

[Anonymous]

Steven,
Not sure I got the problem right, anyway, did you try this:
count( {1<CustomerStore = TransactionStore>} NewCustomer)

[Anonymous]

Stephen,

instead of placing this in the Expression and thus having to have 1 expression for each store, why don't you place CustomerStore in the Dimensions then use the Pivot Table option in Charts.

If i've got this totally wrong I apologise, maybe a demo QV file might help us understand whats going on.

Cheers,

John.