6 Replies Latest reply on Aug 10, 2015 10:21 PM by rakhesh.nellikoppa

# How to Calculate Cross Shopping

Hi All,

I am stuck in finding Cross shopping formula in Tableau (Retail), I am not getting formula or proper definition that would help me to write formula.

Simon you might be having better idea/formula to calculate this Cross shopping would be very helpful for me as I am new to Retail domain.

I have created dummy data set with my best knowledge to explain the scenario, please correct if required. I have attached twbx file but haven't done any calculation

Defination of “Cross Shopping: % of buyers also purchasing from other retailers among its own customers”

 Retailer Shopper Total customer Total Cross Shop Customer B Cross Shopper C Cross Shopper D Cross Shopper A 5 0 0 0 0 B 5 4 0 1 3 C 4 2 1 1 D 5 5 3 2 0

At retailer level

Cross Shopping % is

Retailer A =  0/5 = 0

Retailer B = 4/5  =80%

Retailer C = 2/4 = 50%

Retailer D = 5/5 = 100%

Please help me to get the Cross Shopping if anyone have done this before or any helpful information would be much appreciated.

Thank you.

--Rakhesh

• ###### 1. Re: How to Calculate Cross Shopping

Have attached the twbx

Thank you.

--Rakhesh

• ###### 2. Re: How to Calculate Cross Shopping

Hey Rakhesh,

This is pretty much similar to your SOW question. If you create 3 calculations, you can get it to working as expected.

Calc 1: { fixed [Retailer]: countd([User id]) } - This is counting unique users who bought at a specific retailer.

Calc 2: { fixed [User id] : IF count([Retailer]) > 1 THEN 1 else 0 END } - This willl count all the users whose retailer count is more than a 1, meaning if they bought in more than one retailer.

Calc 3: [Calculation2]/[Calculation1] - simple a division to give you a percent of customers buying at multiple retailers.

So in the above, 5 users bought at retailer B, out of which 3 of them also bought at other retailers. So (3/5 which is 60% of retailer B's users are cross shoppers. I am not sure how you are getting a 80% in your mock up above.

Similarly for other retailers. You can move around your fields on sheet 2 to bring retailer before the user id field on rows to see how many users bought at a specific retailer. This is just an example of what could be accomplished with LOD expressions. You can also fix the purchases at a level of date if you need to find the ratio on a month level as well.

Hope this helps!

• ###### 3. Re: How to Calculate Cross Shopping

Rakhesh,

Did this solution help? LOD is a pretty hot topic in the community. Can you mark the answer correct if you got the solution to your problem so that other users can benefit from this as well?

Thanks,

1 of 1 people found this helpful
• ###### 4. Re: How to Calculate Cross Shopping

Yes Pooja, was checking with my data and no doubt its working wonderful .....

I am glad and really thankful to you ... Thank you very much.

Regards,

Rakhesh

• ###### 5. Re: How to Calculate Cross Shopping

Hi Pooja,

Good Morning ...

Can you help me to calculate the rivals of any retailers, example as per your solution book, Book8 – PG. Out of 4 users of “Retailer C” 2 users did cross shopping with Retailer B and Retailer D. Can we able to find the rivals of Ret A is Retailer B and D and count of my customers.

Total Retailer of C – 4

Retailer C to B – 1 (userid 107)

Retailer C to D – 1 (Userid 112 )

Thanks in advance ….

-- Rakhesh

• ###### 6. Re: How to Calculate Cross Shopping

Hi ...