8 Replies Latest reply on May 9, 2018 5:15 AM by Rahul Singh

# Using LOD calculation in data join

Hello,

I am new to tableau and unable to find solution to a very basic problem. I have a master data containing list of county and state (call it demographics data) and another data on sales in these states and county. Now, i joined the two data using left join and trying to calculate total sales in a state with county names also in the view using LOD calculation. However, i am getting multiple sales value in a State. I don't know how to get uniform value across each county by modifying calculation or using some other method

I have used this LOD function : { FIXED [State],[Fiscal Year]:SUM([Sales])} . I have added fiscal year to LOD as i need to use filter on year/quarter/month level in actual project.

e.g in state Alabama i am getting value as 376, 268, 290 etc.

Attached tableau packaged file and excel file.

Thanks,

Rahul

• ###### 1. Re: Using LOD calculation in data join

How you are joining the two table?

demographic left join sales or
sales left join demographics

• ###### 2. Re: Using LOD calculation in data join

Hi, Rahul

I think you need remove the Fiscal year in the LOD as not every county has sales each year.

ZZ

• ###### 3. Re: Using LOD calculation in data join

I have added Demographic in left

• ###### 4. Re: Using LOD calculation in data join

The ideal way of joining transactional and master data is, transactional table left join master table and on the unique/primary key of the master table.

try this approach along with what ZZ suggested.

1 of 1 people found this helpful
• ###### 5. Re: Using LOD calculation in data join

Hi, removing fiscal year doesn't serves the purpose. For the actual work, i have to take filter fiscal year/fiscal quarter and that's why i have added this to LOD

• ###### 6. Re: Using LOD calculation in data join

By taking transaction data on left, i would be missing few counties/cities because they might not have recorded any sales in particular fiscal year. Going more granular level (months) i will be missing even more data points. I want a view of total sales in sate and county next to each other with option to filter on fiscal year/quarter. The county which didn't recorded any sales should give me null or zero value

Thanks

Rahul

• ###### 7. Re: Using LOD calculation in data join

Hi, Rahul

If fiscal year is a filter, you can add to context filter as shown below

ZZ

• ###### 8. Re: Using LOD calculation in data join

Thanks ZZ, this certainly helps.