1 Reply Latest reply on Dec 2, 2016 6:32 PM by Shinichiro Murakami

    Total Census Figures by Time Period LOD

    ernesto debeaumarchais

      I have total census figures by month, and I calculate an average daily census by that total census number/the number of program days in the month:

          

      DateDays_in_MonthcensusMTDQTD
      1/1/2016201015.1
      2/1/2016212019.6
      3/1/2016231024.46.3
      4/1/2016212039.7
      5/1/2016221034.7
      6/1/2016221044.76.3
      7/1/2016201045.2
      8/1/20162350121.8
      9/1/2016211024.911.0
      10/1/2016211055.0
      11/1/2016221014.6

       

      For a quarterly figure, it's (censusQ1+censusQ2+censusQ3)/(days in monthQ1+days in monthQ2+days in monthQ3), and so on.

       

      I have a "Period to Date" field that I use to break out into custom date frames instead of just months, like WTD, MTD, QTD, etc.  Then it's a little trickier because, to get the Days in Month right, you need to sum the max values for each quarter.  To clarify, the data above is simplified.  Imagine that's a sum total comprised of many records.  I put the 'days in month' on every record.  For a basic calculation, it's easy, as you can just take sum(census)/max(days in month).  But when you combine it with the 'Period to Date', you now need to get the (max) value of days in month for each time period and then add them to get the total days in period.  See the attached.  'Days in Period' is what I'm trying to calculate.  I imagine it's some kind of Fixed statement - I got something like this to work elsewhere, but I think the Period to Date thing adds another wrinkle.  Any ideas?

        • 1. Re: Total Census Figures by Time Period LOD
          Shinichiro Murakami

          Hi Ernesto

          Based on given table format, I will provide this approach.

           

          Create start date for each month, quarter , year for Month, Quarter, and Year.

           

          [Date start Month]

          date(datetrunc('month',[Date]))

           

          [Date Start Quarter]

          date(datetrunc('quarter',[Date month]))

           

          [Date Start Year]

          date(datetrunc('year',[Date month]))

           

           

          Pick up one data from each month to represent

           

          [Days in month 2]

          if [Date]={fixed [Date month]:min([Date])} then [Days in Month] end

           

           

          Calculate days in each period

           

          [Days in Quarter]

          {fixed [Date Start Quarter]:sum([Days in month 2])}

           

          [Days in Year]

          {fixed [Date Start Year]:sum([Days in month 2])}

           

           

          Average for each period

           

          [Average Census Month]

          sum([Census])/max([Days in Month])

           

          [Average Census Quarter]

          {fixed [Date Start Quarter]:sum([Census])}

          /

          [Days in Quarter]

           

          [Average Census Year]

          {fixed [Date Start Year]:sum([Census])}

          /

          [Days in Year]

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful