sort day of week data categories correctly

[bminehart]

When creating widgets using the day of the week as a category, the categories are sorted alphabetically by the day of the week (Friday, Mon, Sat, Sun, Thu, Tue, Wed) instead of sorting by the actual order of the days of the week (Sun-->Sat, or Mon-->Sun). Seems like an obvious, and absent, feature.

[Laflet]

Please Refer to:
https://community.sisense.com/t5/deploy-connect-analytics/ordering-pivot-weekday-column-mon-sun/m-p/...

You can add a field to the cube, in which you can add spaces before the names of the days.
They are not displayed on the visual, but the space will be in the first place when sorting in ascending order.
You can add two spaces and it will come before one space.
Add this column to the table and see the right order.
The result column will be like this:

'   monday'
'   tuesday'
'  wednesday'
' thursday'
'friday'
'saturday'
'sunday'

[Helena_qbeeq]

Hi @bminehart ,

I agree this would be a great feature to have in Sisense.

In the meantime, you could also create a custom column as shown here.

Instead of using a text with the name of the day, you will have a custom column with a date. All values will range in the same week and will aggregate correctly as you wish. The upside is that you can then format the date value as you wish and still keep the correct regional setting at all times, while also sorting the days correctly.

Please let me know if this works for you : )

Always here to help,
Helena from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner

[bminehart]

Thanks @Laflet and @Helena_qbeeq -- we exclusively use a live data connection and don't use elasticubes whatsoever. Is there an alternative to your suggestion, that could be implemented at the Sisense layer?

[Laflet]

Simply do this in the source. 

If this is sql use a case statement. This will require you to use a view.

Case

when value = 'monday' then '   monday'
when value = ' tuesday' then '   tuesday'

etc

END

as OrderedValue

[Helena_qbeeq]

Hi @bminehart ,

As @Laflet mentioned, you would have to implement the logic in your view.

The only difference between our solutions is that the first one will return a string with the name of the day, and the second will return a date.

Instead of returning "Monday", you can pick a random week in the calendar, and associate all mondays in your data with that monday in your week.

Let's say we look at this week:

Monday: July 3rd of 2024, Tuesday: July 4th, Wednesday July 5th... And so on.

So the query would be something like Case when DayOfWeek=monday then 2024/07/03 when Tuesday then 2024/07/04 etc.

When you use that new column in your dashboard, all the rows will be grouped to those 7 days, and you can then format it as you wish. The upside when using this method is that regional settings will apply correctly. So if one of your users is french for example, it will show the names of the days in french. It will also sort the days correctly depending on the first day of the week you have defined in your settings.

Please let me know if this works for you : )

Always here to help,
Helena from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner