Hi,
I have an issue that other forum posts have not helped me resolve. Any help is greatly appreciated!
I am using a Date Grouping formula to change the "grouping" within a Group based on if Daily, Weekly, Monthly, Yearly, or Hourly is chosen.
For the weekly part of the formula, I have used this:
if mp= "Weekly" then totext(dateadd("ww",datepart("ww",{AGENTACTIVITYLOG.STATUSDATETIME})-1,date(year({AGENTACTIVITYLOG.STATUSDATETIME}),01,01)-dayofweek(date(year({AGENTACTIVITYLOG.STATUSDATETIME}),01,01))))
However, when using this formula, it remove the sorting. For example, I want this sorted by date, but it sorts it numerically by the first number.
10/1/2016
8/27/2016
9/10/2016
How can I use this forumla and change the sorting so that it is in order by date?
Thank you in advance for your assistance with this!
Another way to handle this if you have to have a text date is to format the date as 'yyyyMMdd', which will put the dates in the correct order.
-Dell
Thanks Dell! Would you mind assisting me on where I would make this change in my formula above? Thanks for the assistance!
If you look at the Help, there are lot's of format string possibilities when using ToText. So, for your formula, it would look like this:
totext(dateadd("ww",datepart("ww",{AGENTACTIVITYLOG.STATUSDATETIME})-1,date(year({AGENTACTIVITYLOG.STATUSDATETIME}),01,01)-dayofweek(date(year({AGENTACTIVITYLOG.STATUSDATETIME}),'yyyyMMdd'))))
Please note that the format string is case sensitive - you must capitalize the 'MM' to get the month.
-Dell
Unfortunately, I am unable to get this to work. Thanks for your assistance.
Hi Matt,
Try this please:
1. Remove the totext() function from the formula above. Make sure you remove it from each of the if-else conditions. The formula would be:
if mp= "Weekly" then dateadd("ww",datepart("ww",{AGENTACTIVITYLOG.STATUSDATETIME})-1,date(year({AGENTACTIVITYLOG.STATUSDATETIME}),01,01)-dayofweek(date(year({AGENTACTIVITYLOG.STATUSDATETIME}),01,01)))
2. Go to the Group Expert > Click the formula in the Group List > Options > Option tab > Check 'Customize Group Name field' > Click 'use a formula' and click the formula button beside > Use this code:
Totext({@formula name used as group})
-Abhilash
Thank you! However, for this formula, it is telling me "A string is required here". Do you know why this is occurring? Thanks again for your help!
Here is the entire formula if that will help at all. When I took out the "totext" I could no longer use "mdf", which caused all of these to not work.
datetimevar mdf:= {AGENTACTIVITYLOG.STATUSDATETIME};
stringvar mp:= {?Date Grouping};
if mp = "Daily" then totext (mdf, "MM/dd/yyyy")
else
if mp = "Monthly" then totext (mdf, "MM/yyyy")
else
if mp = "Yearly" then totext (mdf, "yyyy")
else
if mp = "Hourly" then totext (ININ_FormatTimeToStrSort (Time(hour({AGENTACTIVITYLOG.STATUSDATETIME}),00,0))) else
if mp= "Weekly" then totext (dateadd("ww",datepart("ww",{AGENTACTIVITYLOG.STATUSDATETIME})-1,date(year({AGENTACTIVITYLOG.STATUSDATETIME}),01,01)-dayofweek(date(year({AGENTACTIVITYLOG.STATUSDATETIME}),01,01)))
Looking at the full formula, try this to get everything sorting in the correct order. I changed the week calculation so that it gets the date of the first day of the week and works from there.
datetimevar mdf:= {AGENTACTIVITYLOG.STATUSDATETIME};
stringvar mp:= {?Date Grouping};
stringvar result := '';
datetimevar weekStart;
if mp = "Daily" then result := totext(mdf, "yyyyMMdd")
else
if mp = "Monthly" then result := totext(mdf, "yyyyMM")
else
if mp = "Yearly" then result := totext(mdf, "yyyy")
else
if mp = "Hourly" then result := totext(ININ_FormatTimeToStrSort (Time(hour(mdf),00,0)))
else
if mp= "Weekly" then
(
weekStart := dateadd('dd', -1*DatePart('dd', mdf)+ 1, mdf); //get the first day of the week
result := ToText(weekStart, 'yyyy') + ToText(DatePart('ww', mdf), '00'); //year and week number
);
result
-Dell