4 Replies Latest reply on Jun 28, 2013 5:57 AM by Vincenzo Manzoni

# How to compute a row from two?

Dear all,

I have a table such this:

 code signal operation value 335145 PQF_J6_SF_DP Mean 39,90 335145 PQF_J6_SF_DP Max 183,63 335146 PQF_J1_SF_DP Max 2180,82 335146 PQF_J1_SF_DP Mean 2013,08

I would like to add the two new rows listed hereafter:

 335145 PQF_J6_SF_DP Perc 183.63 / 39.90 * 100 335145 PQF_J6_SF_DP Perc 2180.82 / 2013.08 * 100

Is there any way to do it in Tableau?

Thanks all!

- Vincenzo

• ###### 1. Re: How to compute a row from two?

Vincenzo,

Sure!  It can be done.  I've attached a workbook to show one way of doing it.  The workbook contains inline documentation, but the basic idea is to create a calculated field, which is a table calc, to use values from each of the pair (Mean row and Max row).

If you aren't too familiar with table calculations, you might want to do some browsing through the knowledge base.  The main thing is to get the table calcs setup with the right addressing / partitioning.  In this case the calculation and setup looks like this (the workbook contains more detailed explanation):

Regards,

Joshua

1 of 1 people found this helpful
• ###### 2. Re: Re: How to compute a row from two?

Hi Joshua,

I tried another way:

1. I created two computed fields as follows:

• Max_comp: IF [operation] = 'Max' THEN [value] END
• Mean_comp: IF [operation] = 'Mean' THEN [value] END

2. I created a third computed field, Max / Mean, as [Max_comp]/[Mean_comp].

While I can plot histograms of Max_comp and Mean_comp, I cannot plot the histograms of Max / Mean.

I attached the workbook. Can you please have a look at it?

Thanks!

Best,

- Vincenzo

• ###### 3. Re: Re: How to compute a row from two?

Vincenzo,

The reason that is not working is that your third calculation is a row-level calculation.  That means that it is calculated for each row of data from the source.  But if you look at the source data, either Max or Mean is NULL in each row, so Max / Mean will always be NULL (see image below).

Do you have any ability to "re-shape" the data at the source level and get Max and Mean in the same row (for each code / signal)?  Then it would be very easy to do what you want.

If that's not possible, You could do an aggregated calculation: MIN(Max_Comp) / MIN(Mean_Comp)

However, there are some limitations:

1. You won't be able to create ad-hoc bins (you could do a calculated bin)

2. You will have to make sure your view is at the level of code and signal (meaning that those two fields will have to be present in the view on Rows, Columns, Color, Level of Detail, etc... to define that as the level of aggregation.)

I'm guessing that those limitations (which also exist for the original table calculation solution) will mean that it is not really going to be a good, viable solution.  So, if there is any way to re-shape the data and get Max and Mean in the same row, that really will be best.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 4. Re: How to compute a row from two?

While I was waiting your reply, I did what you suggested: I pivoted the source table to have mean and max on the same row.