cancel
Showing results for 
Search instead for 
Did you mean: 

Sorting "totext" date formulas to sort by week

Former Member
0 Kudos

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!

Accepted Solutions (0)

Answers (3)

Answers (3)

DellSC
Active Contributor
0 Kudos

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

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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!

Former Member
0 Kudos

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