-
1. Re: Per Day Calculation
Dereck Wate Jun 28, 2016 3:29 AM (in response to Dereck Wate)Hey appreciate any suggestions thanks..
-
2. Re: Per Day Calculation
John Sobczak Jun 28, 2016 4:06 AM (in response to Dereck Wate)I would try to debug it by trying different variables in your logic such as changing to the bold and seeing what that results in
INT((sum([Actual]) /IIF(MAX(YEAR([Period]))<YEAR(NOW()),365,DATEDIFF('day',MIN(DATETRUNC('year',[Period])),Max(Today()))
))/19)
or even what the latter part of your equation by itself yields
DATEDIFF('day',MIN(DATETRUNC('year',[Period])),MAX([Period])+1)
-
3. Re: Per Day Calculation
Dereck Wate Jun 29, 2016 11:19 PM (in response to Dereck Wate)thank you John, using today () gives the following result, as the formula calculates the last date for the entry you can see the wages was entered on 1/June/2016 hence you can see the table below is not giving me the desired result of 182 days this year which I need in my formula,
is there a way to get the number of days based on the month of reporting rather than an entry date
example
Jan = 31
Feb = 59
Mar = 90
Apr = 120
May=151
Jun=181
Account Code (Crew) January February March April May June Joining Cost 31 60 91 121 152 181 Mgmt Cost 31 60 91 121 151 180 Misc Cost 31 60 91 121 152 181 Non Crew 31 60 91 121 152 182 Port Costs 31 60 91 121 152 181 Training Cost 31 60 91 121 152 181 Travel Cost 31 60 91 121 152 180 Victualling 31 60 91 121 152 180 Wages 31 48 61 92 122 153 -
4. Re: Per Day Calculation
Ashish Chaudhari Jun 30, 2016 12:53 AM (in response to Dereck Wate)Hi Dereck,
Please find the attached tableau workbook and confirm the output. I have also attached the excel data source that is being used.
I have used two calc fields which are listed below
Name - Date_Diff (This will give the data diff from year start to today)
DATEDIFF('day',MAKEDATE(YEAR([Year]),1,1),TODAY()-30)
Name - Per Day Calc (This will calculate the Per year separate for 2016)
if [Pivot field names]="Yr 2016" then ([Pivot field values]/[Date_Diff])/19
ELSE ([Pivot field values]/365)/19
END
Let me know if you have any queries. I have used pivot data option to transform the data row wise.
Thanks and Regards,
Ashish Chaudhari
-
day in a year.xlsx 10.7 KB
-
Per Day Calc.twbx 40.2 KB
-
-
5. Re: Per Day Calculation
Dereck Wate Jun 30, 2016 1:43 AM (in response to Ashish Chaudhari)thank you Ashish,
Using your data for the year 2016 the result I am looking for is
Amount / Qty / Days
Cost 1 80 545 /19 /182 = 23.3
Cost 2 302,562 /19 /182 = 87.5
Cost 3 198,714/19 /182= 57.4
for next month it would be
Amount / Qty / Days
Cost 1 80 545 /19 /213 = 19.9
Cost 2 302,562 /19 /213 = 74.75
Cost 3 198,714/19 /213= 49.1
Basically I need the number of days from 1/1/2016 to the end of each month I am reporting on at the end of June it is 182 days at the end of next month it would be 213 days