6 Replies Latest reply on Oct 1, 2013 7:28 AM by John Sobczak

# Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month).

I'm trying to calculate the lake elevation level difference between the first and last day for each month. This will give me a great seasonal weather metric of showing relative wet vs dry months for any set of years.  Right now on 2nd tab I am computing the difference of monthly average lake level between months, which mostly correlates to this, but may not be completely accurate as you may have a back to back extreme wet month where the level increases a lot followed by a net dry month where the lake level decrease only slightly.  This scenario will still show the net dry month as a net increase in monthly average. I believe the way to accomplish this is to use a table calc lookup of first vs last and partition by month, but I also want to address by month.  I just can't get the right combination to work in the advanced dialog box. Attached is the workbook.

• ###### 1. Re: Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month), Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month)

Hey John.

I'm trying to calculate the lake elevation level difference between the first and last day for each month. This will give me a great seasonal weather metric of showing relative wet vs dry months for any set of years.

I'm not sure I agree (understand?) this statement. The last day of Jan is only one day different from the first day of Feb. and presumably lake levels wouldn't change much in a single day. So there really isn't much difference between comparing Jan averages to Feb averages, and comparing Jan 1 to Jan 31.

Or I'm really missing something here.

I think that a table calc of % of total may serve your purpose better. See attached.

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month), Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month)

Shawn,

It is the last day of the month vs the first day of the same month. not the following month.

1 of 1 people found this helpful
• ###### 3. Re: Re: Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month), Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month)

So you said, so I understand. Oh well, I'm sure someone else will better understand what you're trying to accomplish.

Cheers,

--Shawn

1 of 1 people found this helpful
• ###### 4. Re: Re: Re: Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month), Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month)

Shawn and John,

I could replicate startdate of a month  vs end date of a month  variance using datetrunc function.

created couple of calculated fields to understand the equations well, I hope this will help you.

First day of month = datetrunc('month',[Date])

Last Day of month = Dateadd('day',-1,datetrunc('month',Dateadd('month',1,[Date])))

• ###### 5. Re: Re: Re: Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month), Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month)

1 of 1 people found this helpful
• ###### 6. Re: Table Calc Difference between First day and Last Day Each Month (partition by month, but also address by month).

. Indumon,

That is ingenious!  Thanks very much for the time you put into this!  I was also thinking more about this and perhaps it is what Shawn was trying to tell me in that I believe there is another way to perform the calc and no need to take both end and start points but rather just the end point of every month (using your last day calc) and do a table calc difference with that which gives me similar results.

1 of 1 people found this helpful