6 Replies Latest reply on Oct 10, 2019 1:40 AM by Ciara Brennan

# Table Calculations

Hello.

Below are the results of a table where I basically require a way to calculate the percentage of numbers that had a decrease between "Initial" and "Discharge".  I was hoping there is a table calculation, or series of calculations that could get me this result.  Only results with an "Initial" number > 0 should be counted (all highlighted in yellow is what I want....not the orange one).  My end result would be seeing that 5 out of the 6 yellow instances had a drop in number between "Initial" and "Discharge"  (5 / 6 = 83%) for that particular ID.   I would then require the percentage at the Program level as well for a total.

EDIT:  .twbx sample file attached

Any help would be greatly appreciated!

Thanks

• ###### 1. Re: Table Calculations

Something like:

{FIXED Program, ID, Initial >0:

INT({FIXED Program, ID,3rd column, QUARTER(Date) : SUM(Initial)} - {FIXED Program, ID,3rd column, QUARTER(Date): SUM(Discharged) >0)/ INT({FIXED Program, ID,3rd column, QUARTER(Date) : COUNT(Initial)}

And then add a Context filter to filter out the Initial ==0

I Would need data to check it but it should be close

• ###### 2. Re: Table Calculations

Hi Will.

I guess I should have clarified that the 3rd Column doesn't have a variable.  Those are all independent measures so they don't belong to any 1 Dimension or Measure as a group.  That's why I was hoping a table calculation could be useful.  I'll try to post some data when I get a chance to see if that helps.

Also, "Initial" is the result of a Dimension, not a Dimension itself.  Sorry, I know it's not helpful to not have any data to play with.

I appreciate where you're going with this.  I'll experiment with it a bit.

Jim

• ###### 3. Re: Table Calculations

ah,

I'd need to see how your data was set up but im guessing window calculations could help

• ###### 4. Re: Table Calculations

Okay I've attached some sample data.

As per my original posting, I'm looking to get the percentage that decreased from the Survey Type of initial to discharge.  In the below example ID 30112 has 4 of 6 that decreased, or 66%.  The one that went from 0 to 3 should be excluded.  Where survery = initial and the total is zero, it's not included in the calculation.  A window calculation may do the trick??

• ###### 5. Re: Table Calculations

Ahh ok yeah I had a look but I think the best way might be to try and reshape your data. It might work if you can convert your match fields to a column otherwise you are stuck with the problem that you cant use measure values in calculations

• ###### 6. Re: Table Calculations

Hi Jim

If you have a couple of minutes could you please take this quick survey around your experience on the Tableau Community Forums?

Much appreciated
Ciara

[Program Manager | Tableau Community Forums]