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

    Per Day Calculation

    Dereck Wate
    Dereck Wate Jun 19, 2016 2:55 AM

      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
      20132014201520162013201420152016
      Cost 1301,061196,392203,72580,54543282928
      Cost 2665,155712,940709,409302,56296103102105
      Cost 3575,741414,456448,808198,71483606569
      Cost 4659,743604,620577,370289,665958783100
      Cost 5794,406316,275567,866296,5431154682103
      Cost 61,242,9881,287,2691,234,948554,060179186178192
      Cost 71,711,4431,616,3211,620,662677,392247233234235
      Cost 822,777,68822,636,52022,727,4379,817,4663,2843,2643,2774,235
      Grand Total28,728,22527,784,79328,090,22412,216,9464,1424,0064,0504,230

       

       

      Appreciate any suggestions

      Thanks Dereck

      • 700 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Per Day Calculation
          Dereck Wate
          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
            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
              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)JanuaryFebruaryMarchAprilMayJune
              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
                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

                • 5. Re: Per Day Calculation
                  Dereck Wate
                  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