5 Replies Latest reply on Jun 30, 2016 1:43 AM by Dereck Wate

# Per Day Calculation

I am having a problem with the way per day cost is being calculated for the years 2013 to 2015 the calculations are fine however for the year 2016 only "Cost 8" shows a wrong calculation all the others are correct the result is 3,399 however is calculated as 4,235

calculation for prior years = Actual / 365 / 19

calculation for current year = Actual / 152 /19 (data as of 31-May-2016

My formula for per day is as follows

INT((sum([Actual]) /IIF(MAX(YEAR([Period]))<YEAR(NOW()),365,DATEDIFF('day',MIN(DATETRUNC('year',[Period])),MAX([Period])+1)

))/19)

 Actual [Per Day]/19 2013 2014 2015 2016 2013 2014 2015 2016 Cost 1 301,061 196,392 203,725 80,545 43 28 29 28 Cost 2 665,155 712,940 709,409 302,562 96 103 102 105 Cost 3 575,741 414,456 448,808 198,714 83 60 65 69 Cost 4 659,743 604,620 577,370 289,665 95 87 83 100 Cost 5 794,406 316,275 567,866 296,543 115 46 82 103 Cost 6 1,242,988 1,287,269 1,234,948 554,060 179 186 178 192 Cost 7 1,711,443 1,616,321 1,620,662 677,392 247 233 234 235 Cost 8 22,777,688 22,636,520 22,727,437 9,817,466 3,284 3,264 3,277 4,235 Grand Total 28,728,225 27,784,793 28,090,224 12,216,946 4,142 4,006 4,050 4,230

Appreciate any suggestions

Thanks Dereck

• ###### 1. Re: Per Day Calculation

Hey appreciate any suggestions thanks..

• ###### 2. Re: Per Day Calculation

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

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

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

• ###### 5. Re: Per Day Calculation

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