7 Replies Latest reply on Jan 8, 2014 10:08 AM by Renato Rocha

How can i create a average calculation based on months

Dear all,

My company sells a lot of products in a daily basis and i need to create a average sales based on monthly basis to discover which is our average monthly consumption by month. But, i don't understand how Tableau calculates the average or if i have do create some calculation filed.

Please, hope anybody could help me.

attached the sheet that i have to use to calculate.

Regards,

Renato

• 1. Re: How can i create a average calculation based on months

Can you re-post your workbook as a packaged workbook file (.twbx)?  File>>Save As>> Tableau Packaged Workbook.

We do not have access to your data (extract file), so we cannot open your workbook -- a packaged workbook will include your data source in the file.

You should be able to represent any date field in a MONTH format, and any measure can be made into average--either by writing a calculated field: AVG([FieldName]), or by simply setting the aggregation for that measure in your worksheet/view as AVG.

• 2. Re: How can i create a average calculation based on months

Matthew Lutton

Tks for the information, follow attached the new file.

• 3. Re: Re: How can i create a average calculation based on months

Are you just trying to change SUM to AVG on the Quantidade field?

If so, you just right click on the pill that says SUM(Quantidade) and change the aggregation to AVG:

If you always want that field to be displayed as an average, you can also right click on the field name in your data window, and choose Default Properties>>Aggregation>>Average

• 4. Re: Re: How can i create a average calculation based on months

Matthew,

In fact, i need to calculate the inventory turnover. So i need to create a average from my sales by product divided by months in the year.

If i use your option, i will just create the monthly average.

Regards,

Renato Rocha

• 5. Re: Re: How can i create a average calculation based on months

Not sure I understand--can you explain the logic for the calculation you want to create, as well as what your desired end result would look like?

For each month/product combination, you want to display the Average Sales by Product for THAT month divided by months in the year?  Or the total sales for that product across all months, divided by the # of months in the year?  Again, I'm not clear on what you are wanting as an end-result: perhaps a mock-up of the final view you want to create would help.

Also, when you say "Sales" do you mean "Quantidade"?

• 6. Re: How can i create a average calculation based on months

Again, I'm not sure what you want your final sheet/view to look like, but hopefully the attached will help.

I've defined an Inventory Turnover calc in the workbook, based on your description.  I've displayed the years in the view, with the products, and have placed Avg(Quantidade), Count of Months,and Inventory Turnover on the text shelf for you to see what is happening.

But again, this is just what I came up with while unsure of what you want to display in the view.  This is based on the assumption that you want to take the average quantidade over all months in the year, and divide it by the # of months in that year.

If this doesn't help, please help clarify what you want to display, and any logic used for calculations that doesn't match what you see here.

Cheers.

• 7. Re: Re: How can i create a average calculation based on months

Matthew,

Excellent job !!! I'm impressed.

But in fact, it was my mistake and i don't show you all informations that i required. I've attached a sheet called "Consumo" this is what we use today in excel, but every day we have to extract informations from our ERP system to calculate it.

At Excel spreadsheet you will see

Column A - Our product code

Column B - Our product name

Column C- Our actual inventory, this data comes from our ERP in a daily basis

Column D - Our imports that were shipped but not arrival at our warehouse yet, this is another database that we update in a weekly basis.

Column E - The total of our "Inventory" - Column C + Column D

Column F - Our Sales in a daily basis, this data comes from our ERP in a daily basis too

Column G - Sales average, in excel file we divided the total by months in years, if you see our formula we are considering the year of 2013 to now.

Column H - Turnover - This is just Column E divided by Column G - This filed show us how many months of inventory we have yet

Column H - We create a matrix that indicates if Turnover is between 0 and 3 months - Priority 0 to Import (red)

If turnover between 4 and 5 months - Priority 1 to Import (yellow)

if turnover more than 6 months - Priority 2 to Import (green).

Now it will be more easy for you to understand that i have to create for my company.