5 Replies Latest reply on Feb 29, 2016 3:19 PM by Romil Shah

How to create a calculated field containing aggregated function

Hi,

I am creating different views for different countries.

Each view(worksheet) contains last 3 year revenue by region for each country, which is divided by total no of working days for corresponding year in USA.

For e.g. First View is USA, Second is UK and Third View is Russia.

First view - Columns would be "Total Revenue in USA in 2013" , "Avg Daily Revenue in USA in 2013", "Total Revenue in USA in 2013=4" , "Avg Daily Revenue in USA in 2014", "Total Revenue in USA in 2015" , "Avg Daily Revenue in USA in 2015".

Here, Avg Daily Revenue= Total Revenue / No of working days in USA for that period

Similarly, for second view -  "Total Revenue in UK in 2013" , "Avg Daily Revenue in UK in 2013", "Total Revenue in UK in 2013=4" , "Avg Daily Revenue in UK in 2014", "Total Revenue in UK in 2015" , "Avg Daily Revenue in UK in 2015".

Here, Avg Daily Revenue would again be = Total Revenue / No of Working days in USA for that period (even though the country is different now).

Hence I would be using "Total No of Working days in USA" in many worksheets. How do i create a calculated field which can be used across many views?

• 1. Re: How to create a calculated field containing aggregated function

Romil,

It would be helpful if you included a packaged workbook.

Thanks,

Ben

1 of 1 people found this helpful
• 2. Re: How to create a calculated field containing aggregated function

Hi Ben,

I have created a dummy excel data and dummy Tableau sheet.

Tableau file contains 3 worksheets - one for each region UK, US and Russia. "2013 Revenues", "2014 Revenues" and "2015 Revenues" are calculated fields.

It has total revenue for 3 different years. Now, I want to calculate Average Daily Revenue for each of the three years (for each region).

Avg, Daily Revenue = Total Revenue for a period / Total Working Days in the same period.

Numerator would be dependent on the region (US/UK/Russia), but denominator would be total working days in US in a period

Hence, denominator is independent of region.

Excel has a column named "Working Day" which is computed based on "Weekday", "Price" & "Revenue" (For a non working day, both price and revenue would be 0).

Regards,

Romil

• 3. Re: How to create a calculated field containing aggregated function

Pooja Gandhi Can you please check

• 4. Re: How to create a calculated field containing aggregated function

Hey Romil!

Sorry, I do not have access to Tableau at the moment. Knowing how fast and awesome this community is to respond to questions, I am sure someone will jump in to assist. Just in case nobody does, then I will take a look tomorrow.

Pooja.

• 5. Re: How to create a calculated field containing aggregated function

No Problem Pooja. Thought of asking you as you had helped me few days back.

Ben,

I have attached updated tableau file. Created some calculated fields for trading days calculation.

As you can see Trading Days and Avg Daily Revenue for UK and Russia is blank/zero because of region filter. So I want denominator to be independent of region.

This is because as per the business rules only US working days should be used to calculated average daily revenue for all regions.

Regards,

Romil