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

# Total Census Figures by Time Period LOD

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:

 Date Days_in_Month census MTD QTD 1/1/2016 20 101 5.1 2/1/2016 21 201 9.6 3/1/2016 23 102 4.4 6.3 4/1/2016 21 203 9.7 5/1/2016 22 103 4.7 6/1/2016 22 104 4.7 6.3 7/1/2016 20 104 5.2 8/1/2016 23 501 21.8 9/1/2016 21 102 4.9 11.0 10/1/2016 21 105 5.0 11/1/2016 22 101 4.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

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