on 10-11-2016 2:27 PM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)))
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.