0

Sort Day of Week Chronologically not Alphabetically

Good afternoon,

I am trying to create a Table Klip that shows AdWords campaign performance by day of the week Sun thru Sat.  When the data is returned with a GROUP statement, (see screenshot) it sorts alphabetically. (see screenshot-2) Column @J:J is a column of strings containing the Day of Week. (Sunday, Monday, etc...) I also have a Day column containing the date in yyyy-MM-dd format. (column @I:I)

I have tried calling some different DATE functions using the Day column but it basically returned the same alphabetical order sorting.

Does anyone have any suggestions on how to sort the Day of Week grouped chronologically? (either Sun thru Sat or Mon thru Sun) 





6 comments

  • Avatar
    Janice Janczyn Official comment

    Hi Rick,

    As you've observed, the GROUP function returns results sorted alphanumerically. You can use the DATE_UNITVALUE function to return the numeric value of the day of the week:

       DATE_UNITVALUE( DATE( SLICE( I:I ), 2, -1), "yyyy-MM-dd" ), day of week )

    This returns 0 for Saturday, 1 for Sunday....6 for Friday. Some arithmetic is needed to start the week on Sunday, such as MOD( DATE_UNITVALUE( ... )+6, 7 ) +1

    Using our new Filter, Group, Aggregate & Sort Actions may simplify your formulas. If you use this approach, you can add a Hidden Data column for the DATE_UNITVALUE function to return the numeric value of the day of the week.

    Happy Dashboarding!

                                  Janice

  • 0
    Avatar
    Rick Henry

    Hi Janice,

    That helped me with the table layout. I was able to make hidden column to sort the data the correct way.

    Now I am stuck with how to use Day of Week as the X axis of a Bar/Line chart where it shows the name of the day and not 0-6. (I am able to display the days in order, I just can't display in order and have the X-axis to show Mon, Tues, Wed...)

     

  • 0
    Avatar
    Janice Janczyn

    Hi RIck,

    You could hard-code your x-axis to be ARRAY( "Sunday, Monday, Tuesday...Saturday" ), then for each series, do a LOOKUP from the x-axis into your data. For example, if weekdays are in column J and Conversions are in column A, your Conversion series would be (where the ! indicates a REF):

       LOOKUP( !x-axis,
                        GROUP( J:J ).
                        GROUPBY( J:J, A:A ) )

    Thanks,
           Janice

     

     

  • 0
    Avatar
    Rick Henry

    How do I display the Array in the X Axis label fields? 

  • 0
    Avatar
    Satish Shewale

    Hi Rick,

    You can simply use following formula to display your days on X-axis.

     

     

    You can use LOOKUP to align data in the Series formula.

    Common Bar/Line Chart scenarios: Example of using LOOKUP to align data

     

    Cheers,

    Satish.

     

  • 0
    Avatar
    Rick Henry

    I got it to work after I created a new Bar/Line chart. I was trying to replace the original formula with Array but it would only display seven "0"s instead of the array data.

    Thanks to everyone for your help!

Please sign in to leave a comment.