@takashi I have a dataset like this:
DateValue01/01/2018201/01/2018001/05/2018301/12/2018102/01/2018302/02/20184I want to create an attribute for Daily Average Value in Each Month, and store the daily average (sum of values within each month ÷ number of days in that month).
The denominator is where I'm having trouble. Any suggestions on the best way to find the number of days within a given month?
I know how to do this in Python
from calendar import monthrange
monthrange(2012, 2)
(2, 29)If you didn't want to use python, you can get the first day of the month then add a month and take away the first date to get number of days
You can also use something like the following in an inline querier if your dates are in ISO format
select aa.*, strftime('%d',date(aa."date",'start of month','+1 month','-1 second')) as days_in_month from Table1 aa;
If you didn't want to use python, you can get the first day of the month then add a month and take away the first date to get number of days
Depending on locale, the format might be %d/%m/%Y. Please specify the format explicitly, in this kind of question.
Thanks @egomm and @takashi, I think this should work!
Since I've become such a heavy FME user, I almost always convert all dates to FME format, %Y%m%d before transforming or trying to calculate anything.