1 Reply Latest reply on Oct 17, 2018 11:11 AM by Nick Parsons

    Calculations with Date Hierarchy

    kevin.palencar.1

      I have a date hierarchy of Year-Quarter-Month-Day. When I roll up the hierarchy from day to month, i want the value displayed to be the sum of the value from the last day of the month, not a total of each day. I would like this to be the same for Year and Quarter to show the sum of the value from the last date.

       

      Attached is sample workbook.

        • 1. Re: Calculations with Date Hierarchy
          Nick Parsons

          Here are some calculations and .twbx suing superstore that may get you started, not sure about the drill-down though.

           

          To get last day of the year...

          DATEADD('day', -1, DATEADD('year', 1, DATETRUNC('year',[Order Date])))

           

          Quarter...

          DATEADD('day', -1, DATEADD('quarter', 1, DATETRUNC('quarter',[Order Date])))

           

          Month...

          DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month',[Order Date])))

           

          And calculations to get sales for those dates...

          SalesForLastDayYear...

          IF [Order Date] = [OrderDate_LastDayOfYear] THEN [Sales] END

           

          SalesForLastDayQuarter...

          IF [Order Date] = [OrderDate_LastDayOfQuarter] THEN [Sales] END

           

          SalesForLastDayMonth...

          IF [Order Date] = [OrderDate_LastDayOfMonth] THEN [Sales] END