How to divide a month into weeks and days to analyze data?

[Joker_Hazard]

Hello everyone! 

Im am struggling with trying to divide a month into weeks and days, due to the fact that each month ends with 30 days or 31 days, and weeks..

My goal is to segreggate this to be able to analyze this data and put it in a histogram or any other type of graph. However I'm unable to do that with the way the dates are in the database.

Any ideas to change this data into something that I can use in graphs that show historical information?

Thank you very much

[apathetichell]

Datetimeformat([date],"%W") does a Monday week and "%U" does a Sunday.

if you need week in a month - you can try something like:

tonumber(datetimeformat([date],"%W"))-tonumber(datetimeformat(datetimeadd([date],-tonumber(datetimeformat([date],"%e"))+1,"days"),"%W"))+1

Without testing sample data - I assume it needs the "+1" for the first week.

[Joker_Hazard]

Hey Apathetic..

thanks for the quick response!!

I tried both formulas and I have a few doubts.

1. First formula Result:

When having different years, it seems that the formula "gets lost" any idea to fix that? 

2, Second formula result:

Not sure if the result is correct or if I understood correctly, but what was the formula supposed to do? I see that the yellow data were not supposed to be there, as for the third week "3" had to have only > 14, right?

Thanks my friend

[apathetichell · Accepted solution]

Hi - the formula is designed to give you the week count internally for a year. If you need it over greater than a one year time period I'd recommend either using datetimediff() from your start date (your min[date] field on a summarize tool would have to be used and then appended as a separate column or this could be hardcoded in) to your your final date and then floor dividing that number by 7. I can write out that formula if you need it. In the alternative - you could start a week count at year 1 using the "%U" or "%W" code and then just continue it through to the following years manually. You'd probably need to include some logic structure to tell if the week continued through or became a new week on 1/1.

The second formula gives you a comparison of weeks within a month - ie if you want to compare the third week of April vs the third week of July and you have two dates, that can tell you both dates are in the third week.

[Joker_Hazard]

Awesome!! So much knowledge! Congrats.

Yes please, if you can write the formula down, it would greatly help me in the future.

Thanks once again!

[apathetichell]

floor(datetimediff(datetimetoday(),"2019-01-01","days")/7)

Where datetimetoday() is the day you are looking up - and "2019-01-01" should be your minimum date.