
1. Re: Calculating monthly daily averages of products but for the first month only count days from first date
Alex Martino Oct 12, 2018 4:05 AM (in response to Alex Martino)Mavis Liu can you help with this? Thanks!

2. Re: Calculating monthly daily averages of products but for the first month only count days from first date
Mavis Liu Oct 15, 2018 1:39 PM (in response to Alex Martino)Hi Alex,
First I created a calculated field finding the minimum date per category:
Min Date per Category
{FIXED [Category]:
min(
if [Sales Volume] >=1 then [Date]
END)}
The below counts the distinct number of days which have a sales volume of one or over
Count of Days not null
COUNTD( if { FIXED [Category], [Date]: SUM([Sales Volume])} >=1
then [Date]
END)
The below works out the average you want
Average first day of the month
if MIN(MONTH([Date])) = min(month([Min Date per Category]))
then
sum([Sales Volume])/[Count of Days not null]
ELSE
[Average Sales Units]
END
For slouchie the sum of sales for september (where the days is 1 or over) is 24. There are 10 days in september where the sum of volumes is 1 of over, so the daily average for september is 2.4.
For the annual daily average, I have the below calculation:
Count of Days not null  annual
sum({ FIXED [Category]: COUNTD( if { FIXED [Category], [Date]: SUM([Sales Volume])} >=1
then [Date]
END)})
Then I have the annual daily average:
Annual Daily Average
SUM( { EXCLUDE MONTH([Date]): sum( { FIXED [Category]: SUM([Sales Volume])})})/[Count of Days not null  annual]
So for the annual daily average, the sum of sales volume for Slouchie is 34, the number of days in the year where the volume is 1 or over is 12. So 34/12 = 2.83.
My calculations are included in the worksheet called correct (2):
Are these the results you're looking for?
Thanks,
Mavis

tableau example_v10.2.twbx 770.6 KB
