14 Replies Latest reply on Jun 21, 2016 12:17 PM by zibal jafri

# Use Table data to create new Calculated Fileds

Hello Everyone,

I have three fields Name, Category and Hours. I have created a table in tableau as shown below:

Now I want to create Calculated Fields showing how many productive hours have been spent over each category using formula as below:

For eg,  I want a calculated filed "Eating Percent time" for Chris, Formula :   Eating/ (Total Time - DO Nothing) ===> 3/(25-1)==>3/24===>1/8 or 0.125.

And if I want to know "Do Nothing Percent Time" for Chris, Formula : DO Nothing/Total Time====>1/25===>.04

Regards,

Zibal

• ###### 1. Re: Use Table data to create new Calculated Fileds

Hi Zabir,

I have put together the sheets , please check if that's what you are looking for.

Using all the Categories

Without _DoNothing Category

Thanks,

• ###### 2. Re: Use Table data to create new Calculated Fileds

Thanks for getting back. I think there is a confusion. There is a field 'Total Time' in the category column which has to be used in the formula as below:

Eating % = Eating/(Total Time - Do Nothing ). 'Total Time' field is not the Sum Total of time distributed across the categories. The 'Total Time' which you are using to create Calculated fields are the Sum Total of time spent on each category which is not required. Now can you help me ?

PS: Total Time is a value for 'Category' dimension and not the Sum Total of Hours spent on each category.

• ###### 3. Re: Use Table data to create new Calculated Fileds

Hi Zibal,

Sorry to spell your name wrong in my earlier post

I have come up with the following, not sure if it's the right answer that you are looking for, please have a look at it.

The following has been added for the calculated field : "Get the Denominator"

IF ATTR([Category])= "Do Nothing" then LOOKUP(Sum([Hours]),4)

ELSEIF  ATTR([Category])= "Eating" then LOOKUP(SUM([Hours]),3)-LOOKUP(SUM([Hours]),-1)

ELSE 0

END

And for "Get the Percentage" , the following has been added

SUM([Hours])/([Get The Denominator])

Both calculations are computed using "[Category]"

Hope this helps!

Thanks,

2 of 2 people found this helpful
• ###### 4. Re: Use Table data to create new Calculated Fileds

This was exactly what I needed. Thanks a lot !

Thanks,

Zibal

• ###### 5. Re: Use Table data to create new Calculated Fileds

I need your help with this. I am trying to calculate the row total without including "Do Nothing" values.

I have attached the .twbx for your reference.

Thanks,

Zibal

• ###### 6. Re: Use Table data to create new Calculated Fileds

Hi Zibal,

Can you just right click on the "Do Nothing" header and Choose "Hide"?  TWBX attached as well as a screenshot below, for your convenience.

• ###### 7. Re: Use Table data to create new Calculated Fileds

Hi Matt,

If I hide "Do Nothing", I still get the Total with it's values included as shown below: One screenshot is before hiding, other is after hiding. As we can see, the Grand Total remains the same. I want 'Do Nothing' Values not to be included in the Grand Total and still that column to be visible.

• ###### 8. Re: Use Table data to create new Calculated Fileds

Hi Zibal,

Please see the attached where you can add  [Eating]+[Leisure]+[Sleeping]+[Till Date] and get the [Grand Total]

Thanks,

• ###### 9. Re: Use Table data to create new Calculated Fileds

I understand -- you hadn't mentioned the desired totals.  Just a friendly reminder to always try to include as much detail about your expected outcomes as possible!

• ###### 10. Re: Use Table data to create new Calculated Fileds

Thanks a ton ! That's perfectly working. However, when I am trying to calculate the Grand Total of Percentages (which is a calculated filed to calculate percent of time spent on each category) I am not able to replicate the same process as I am getting errors of mixing aggregate and non-aggregate measures.

Please see the snapshot below for more details: I want to calculate the Grand Total of the calculated Field "Percent" excluding the "DO Nothing" percent value. Can you please help me with this ? I have attached twbx for reference.

Thanks,

Zibal

• ###### 11. Re: Use Table data to create new Calculated Fileds

Hi Zibal,

I couldn't come up with a good solution, I have put together a dahsboard, please see if that helps.

Someone experienced in this group should be able to help you with this one

Thanks,

• ###### 12. Re: Use Table data to create new Calculated Fileds

Thanks for your efforts. The dashboard which you have attached shows the 'Total Percent value' as the sum for all the categories. I am looking for 'Total Percent Value' that excludes the percent value for 'DO Nothing'. Just in case if you missed the question.

Anyway, Thanks for your time. Good Day!

Thanks,

Zibal

• ###### 13. Re: Use Table data to create new Calculated Fileds

Hi Zibal,

If the dashboard I have set up can be used, then please adjust the  "Total Percent" calculated field used in "Sheet 2" as the following

IF FIRST()==-4 THEN RUNNING_SUM(

IF ATTR([Category])='Eating' then ([Percent]) else 0 END

+

IF ATTR([Category])='Leisure' then ([Percent]) else 0 END

+

IF ATTR([Category])='Sleeping' then ([Percent]) else 0 END

+

IF ATTR([Category])='Till Date' then ([Percent]) else 0 END)

ELSE

0

END

Hope that helps!

Thanks,