Skip to Content
0

Sorting "totext" date formulas to sort by week

Oct 11, 2016 at 01:27 PM

148

avatar image

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Dell Stinnett-Christy Oct 12, 2016 at 01:45 PM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Thanks Dell! Would you mind assisting me on where I would make this change in my formula above? Thanks for the assistance!

0

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

0
Dell Stinnett-Christy

Unfortunately, I am unable to get this to work. Thanks for your assistance.

0
Abhilash Kumar
Oct 12, 2016 at 09:26 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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!

0

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)))

0
Dell Stinnett-Christy Nov 02, 2016 at 02:19 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded