Skip to Content
avatar image
Former Member

Sorting "totext" date formulas to sort by week

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 12, 2016 at 01:45 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 12, 2016 at 09:26 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

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

  • Nov 02, 2016 at 02:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded