+2Hello,
I have several thousand records, each with a date and demographic information. I have set up a workflow using the statistics calculator to get a cumulative sum of each demographic column. Where I am hung up though is grouping these records into weeks, and then doing my cumulative statistics for each week.
Any help would be greatly appreciated!
Best answer by dustin
I'm a bit confused by your last post @fchevaillier, but what if we avoided the week number all together? This string in an AttributeCreator should return the date of the previous Monday for any given date. This should provide you with a way to group, as well as a usable date.
@DateTimeAdd(@Value(date),-P@Evaluate((@DateTimeFormat(@Value(date),%u)-1))D)
+30Since the week DateTime format string is no longer supported, python would be the next best option in my opinion. The attached workbench will convert the date into YYYYweek format. From there you should be able to use that wknum attribute in the Group By parameter of the StatisticsCalculator. This assumes that a week is Monday to Monday, and the input date format is YYYYMMDD.
+2Ok, thank you for your suggestion. I'm a little discouraged that a python caller may be my only option. But i'll give it my best go and update you here on what I figure out...
+30Ok, thank you for your suggestion. I'm a little discouraged that a python caller may be my only option. But i'll give it my best go and update you here on what I figure out...
There are probably other methods to get the desired result using transformers such as the DateTimeConverter and DateTimeCalculator. However, I think it would take quite a few transformers to do so. I lean toward python for things like this just to keep the workspace cleaner.
+2There are probably other methods to get the desired result using transformers such as the DateTimeConverter and DateTimeCalculator. However, I think it would take quite a few transformers to do so. I lean toward python for things like this just to keep the workspace cleaner.
Ok, I figured it out!
I created an attribute called YearWeek, and used the DateTimeConverter to take my date from %Y-%m-%d$ to %Y-%W (converting 2022-10-07 into 2022-40, the 40th week in the year). THEN I use a few statistics calculators to get my sum statistics and cumulative sum statistics, grouping on that YearWeek attribute. And it works!!
Now my next problem is to convert this new YearWeek attribute back into a usable date... What are your thoughts?
According to the help, the %W is the "Two digit week of year, with range 00 to 53, starting with the first Monday as the first day of week 01." Now I am looking to get that Monday day back into the date, for each week...
What do you think @dustin ?
+48There are probably other methods to get the desired result using transformers such as the DateTimeConverter and DateTimeCalculator. However, I think it would take quite a few transformers to do so. I lean toward python for things like this just to keep the workspace cleaner.
Be very careful with the first few days of the new year. The week number can be 00, which means the date is part of the last week of the previous year, which can be week 52 or 53.
I used this formula for YearWeekCorrected when @DateTimeFormat(@Value(Date), %W) = 00:
@Evaluate(@DateTimeFormat(@Value(Date), %Y)-1)-@DateTimeFormat(@Evaluate(@DateTimeFormat(@Value(Date), %Y)-1)1231, %W)
+30I'm a bit confused by your last post @fchevaillier, but what if we avoided the week number all together? This string in an AttributeCreator should return the date of the previous Monday for any given date. This should provide you with a way to group, as well as a usable date.
@DateTimeAdd(@Value(date),-P@Evaluate((@DateTimeFormat(@Value(date),%u)-1))D)
+2I'm a bit confused by your last post @fchevaillier, but what if we avoided the week number all together? This string in an AttributeCreator should return the date of the previous Monday for any given date. This should provide you with a way to group, as well as a usable date.
@DateTimeAdd(@Value(date),-P@Evaluate((@DateTimeFormat(@Value(date),%u)-1))D)
Earlier I was saying I converted my date into year-week attributes that I then used to group all of my statistics with. But I think you are onto something, I need to actualy convert my date into an attribute that is still recognized as a usable date but something I can group records by each week.
Thank you for your help @dustin and @geomancer . I am testing out your string right now.
Here are some screenshots that provide some clarity to my above post.
+2I'm a bit confused by your last post @fchevaillier, but what if we avoided the week number all together? This string in an AttributeCreator should return the date of the previous Monday for any given date. This should provide you with a way to group, as well as a usable date.
@DateTimeAdd(@Value(date),-P@Evaluate((@DateTimeFormat(@Value(date),%u)-1))D)
This was it! Thank you very much @dustin !
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
