2 Replies Latest reply on Sep 24, 2018 8:54 AM by Ritesh Bisht

# Left Join Data on Different Levels of Granularity

Hello,

I have unique problem that I would really appreciate your help on.

I have customer register data so every time a customer buys something at a store, I have a line for them in my SQL query that I connected to my workbook. The data has multiple months, years and store locations. I need this customer granularity to be able to see if they qualify for discounts based on certain factors so I am not able to aggregate it to a total monthly customer spend level.

Since I am not able to aggregate the data, when I try to left join a "total catering amount spent" per location which is Excel, I end up having that "total catering amount spent" multiplied by the number of customer lines I have in that location. This total is done at the location level by month so is a lot less granular than the customer data. Is there a way to set this up so I don't have this problem? I think I could make a calculated field and hard code the values but I would prefer not to have to do that since I have over 20+ locations.

I hope that my question made sense and I am happy to clarify if needed!

Thank you so much in advance.

Best,

Sun

• ###### 1. Re: Left Join Data on Different Levels of Granularity

Hi

You have already figured out that having mixed levels of aggregation in your data is not the best way to go - but you can make it work in one of 2 ways - in Tableau

but you are going to need to think through the aggregation process for each calculations - for those that are you plan to aggregate to the "Catered amount spent" level you will have to Sum() the data - then compare it to an avg() or a min() for the catered amount spent -

if you use Tableau Prep you could bring your disaggregated data in as a file and aggregate the data to the higher level then bring in you "Catered amount spent" data and create the Join after the aggregation

Good luck -

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Left Join Data on Different Levels of Granularity

Blending is effective in different levels of granularity

Thanks,

Ritesh