6 Replies Latest reply on Mar 29, 2016 12:08 PM by George smith

# Help with calculation between rows

New to tableau and I have some data from SQL sever that I put in the following format:

About the data: Daily delivery delay code details, Totalcount  is total # of deliveries, Delaycode is # of delays for that delay code. Plant and Type are two dimensions. Basically I want to get a summary of daily (or weekly) ontime delivery % and delaycode % (to total delivery #) with comparison to previous day (or week) ,

Date
Plant
Type
MeaureName
MeasureValue
02/01/2016BBDelayCode12
02/01/2016BBDelayCode312
02/01/2016AATotalCount350
02/01/2016ABTotalCount200
02/01/2016ABDelaycode113
02/01/2016ABDelayCode212
02/01/2016ABDelayCode311
02/01/2016ABDelayCode45
02/01/2016BBTotalCount260
02/01/2016BATotalCount400
02/01/2016BBTotalCount12
02/01/2016BBDelayCode123
02/03/2016

For example, for 02/01/2016, plant A's ontime% is 1-(sum of delaycode1+delaycode2+delaycode3+delcaycode4)/ sum of Totalcount =1-(2+12+3+40+13+12+11+5)/(350+200),

delaycode1%=(2+13)/(350+200). The end result should be like this, the last column can also be colored shape.

02/01/201602/03/201602/04/201602/05/201602/06/201602/07/2016Change from Yesterday
Ontime%95%100%85%89%95%97%2% ^
Delay code110%5%1%1%0
Delay code22%2%6%1%-1%
Delay code33%2%1%2%1%
delay code41%2%-2%

Any help is appreciated!

G.S.

• ###### 1. Re: Help with calculation between rows

Anybody?

• ###### 2. Re: Help with calculation between rows

George - before going too far - can you confirm the output here is as desired based on the data in your sample?

Patrick

• ###### 3. Re: Help with calculation between rows

Sorry Patrick, I am on version 8.3 and could not open your workbook. Can you post some screenshot?

• ###### 4. Re: Help with calculation between rows

You can use an "Alias" to change the Total Count to read "Ontime" if you like.. but the percents for each value should read correctly..

The table calculations utilized in each view is a Pane Down calculation to separate out the Plant values.  Make sure to use this definition for any other calculations created.

If you have a few more days of data to put in a spreadsheet, the calculations for remaining days can be figured.  Calculating the difference between today() values and yesterdays is a seperate sheet that can appear in the same dashboard as a percent difference to the previous day.   Let us know if you need help building that.

Patrick

• ###### 5. Re: Help with calculation between rows

George, You can install 9.3 alongside your 8.3 install to see how this is setup.  If you need 8.3 to startup by default then a reinstall of 8.3 will fix this.  I no longer have a copy of 8.3 installed on my pc so I can not make this up in that  version.

• ###### 6. Re: Help with calculation between rows

Thank you, The layout is exactly what I want. I will try to install version 9 and look at your workbook.

Thanks again