1 Reply Latest reply on Nov 29, 2018 8:20 AM by Cassandra Spurgeon

    How to calculate total minutes by month utilizing parameters and FIXED in calculation

    Cassandra Spurgeon

      I'm working on a calculation to utilize as a denominator in other various equations, but am having difficulties when a user selects dates that fall into more than a single month.

       

      Currently my calculation total's up the entire time a user has selected, regardless of month:

       

      //Number of Days Selected (+1 to account for the first day selected)

      (DATEDIFF('day',MIN([Start Date]),MIN([End Date]))+ 1)

      *

      //Number of Minutes Selected

      DATEDIFF('minute',MIN(DATETIME('1/1/2018 ' + [Parameters].[Start Time])),MIN(DATETIME('1/1/2018 ' + [Parameters].[End Time])))

      *

      //Number of Rooms that are selected after all filters (that were added to context) are applied

      SUM({FIXED : COUNTD([RoomName]) })

       

      If a user selects any amount of days in a single month, it works as expected (7 days selected * 60 minutes selected * 96 rooms)

       

       

      But if I select dates in another month, it keeps totaling to each month's time (October bar doesn't show up due to limited extract, but September should still only show 40320 minutes):

       

      I attempted to utilize the running sum table calculation, but if the category does not have data for a particular day, it doesn't add the minutes for that selected day.

       

      Any ideas?

        • 1. Re: How to calculate total minutes by month utilizing parameters and FIXED in calculation
          Cassandra Spurgeon

          I was able to find and combine several different things other people put together and seems to work for me, please see below:

           

          //Number of Days Selected (+1 to account for the last day selected)

          MAX({FIXED [Status], MONTH([Start Time]) :

              DATEDIFF('day',

                  IF MONTH([Start Date]) < MONTH(MIN([Start Time]))

                  THEN DATETRUNC('month',MIN([Start Time]))

                  ELSEIF MONTH([Start Date]) = MONTH(MIN([Start Time]))

                  AND DAY([Start Date]) < DAY(MIN([Start Time]))

                  THEN [Start Date]

                  ELSE MIN([Start Time])

                  END,

                  IF MONTH([End Date]) > MONTH(MAX([Start Time]))

                  THEN DATETRUNC('month',DATEADD('month',1,MIN([Start Time])))

                  ELSEIF MONTH([End Date]) = MONTH(MAX([Start Time]))

                  AND DAY([End Date]) > DAY(MAX([Start Time]))

                  THEN [End Date] + 1

                  ELSE MAX([Start Time]) + 1

                  END)})

          *

          //Number of Minutes Selected

          DATEDIFF('minute',MIN(DATETIME('1/1/2018 ' + [Parameters].[Start Time])),MIN(DATETIME('1/1/2018 ' + [Parameters].[End Time])))

          *

          //Number of Rooms that are selected after all filters (that were added to context) are applied

          SUM({FIXED : COUNTD([RoomName]) })

          1 of 1 people found this helpful