4 Replies Latest reply on Sep 20, 2018 12:40 PM by Deepak Rai

# which type of join should I use to get the correct calculations from this table and filtering?

Hi everyone,

I am wondering which type of join (or maybe I should use a union) I can use to correctly use the data the way I want for filtering and graphs. I have a large data file and I am having trouble figuring this issue out. Below I made a simple example of two data sets I  have. the finance table and the employee table.

finance table

place

transitactual
forecast
store200105jan
warehouse5002010feb
store10055jan

total

employee table

worker
worker number
placemonthtransitemployee type
tim1storejan200a
jane2storejan200a
jo3warehousefeb500b
jim4warehousefeb500b
yoda10storejan100a

Joined

worker
worker number
placemonthtransitemployee typeactual
forecast
tim1storejan200a105
jane2storejan200a105
jo3warehousefeb500b2010
jim4warehousefeb500b2010
yoda10storejan10a55

As you can see  when joined the actual and forecast columns duplicate the data for all employees that are under the same transit, month and place. so the totals for Jan for example won't be correct. It will show that Jan store transit 200 actual total = 20 and forecast  total = 10 not the correct way which is Jan store transit 200 actual total = 10 and forecast = 5

I want to use the correct actual and forecast numbers and their totals for text tables  for calculated  column variances and the correct monthly totals for graphs.

is there something I am not selecting when making text tables and graphs? or should I make a union which would make this all easier except for making all the columns in the finance table (haha)?

thanks hope I am making sense

• ###### 1. Re: which type of join should I use to get the correct calculations from this table and filtering?

Hi a m

You are doing it Right. Just use this calculation when you want to Sum per month strore transit

ACTUAL SUM

{FIXED month,place,transit:AVG(actual)}

FORECAST SUM

{FIXED month,place,transit:AVG(forecast)}

• ###### 2. Re: which type of join should I use to get the correct calculations from this table and filtering?

Pl See attached

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

• ###### 3. Re: which type of join should I use to get the correct calculations from this table and filtering?

thanks so much for the help Deepak

I have another question for you. I don't know if I should make another topic for it but here goes

what formula can be used to make a calculated field for the variance of the total of ACTUAL SUM and FORECAST SUM for each month?

I tried ATTR([Actual sum]) - [HR Total] but that only works for each transit but it does not work when it's  the totals of the month.

thanks

• ###### 4. Re: which type of join should I use to get the correct calculations from this table and filtering?

Like This?

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread