Creating a punch card panel on dashboard and sorting order?

[AndreasLasses]

Hello!

I am trying to create a punch card panel in a dashboard. I want to sort the X field on week number in ascending order and the Y on weekday in ascending order. My issue is that however I formulate the query, one of the axises will be in descending and the other in ascending. Here is my query:

index="*******" sourcetype=_json
| stats latest(Number_of_Commits) as Commits by week Day
| eval week_sort = case(
  Day=="Monday", 1, 
  Day=="Tuesday", 2, 
  Day=="Wednesday", 3, 
  Day=="Thursday", 4, 
  Day=="Friday", 5,
  Day=="Saturday", 6,
  Day=="Sunday", 7
)
| eval sort_field = week_sort*10 + case(
  Day=="Monday", 1, 
  Day=="Tuesday", 2, 
  Day=="Wednesday", 3, 
  Day=="Thursday", 4, 
  Day=="Friday", 5,
  Day=="Saturday", 6,
  Day=="Sunday", 7
)
| sort sort_field week_sort

The result of this query is as follows:

What am I missing to make both X & Y axises sort in ascending order?

Thanks in advance

[woodcock · Accepted solution]

Like this:

index="_*"
| eval Day=strftime(_time, "%A")
| eval week=strftime(_time, "%V")
| stats count AS Commits BY week Day 
| eval _week_sort = case( Day=="Monday", 1, Day=="Tuesday", 2, Day=="Wednesday", 3, Day=="Thursday", 4, Day=="Friday", 5, Day=="Saturday", 6, Day=="Sunday", 7 ) 
| sort 0 - _week_sort

[AndreasLasses]

Thank you so much for this @woodcock  !
And let's say I want the days in descending order and the week still in ascending?

[VatsalJagani]

I'm not 100% sure but give this a try for the last line of @woodcock 's answer:

| sort 0 -_week_sort, +week

I hope this helps!!!

[woodcock]

Not possible.  You get one or the other.  Don't forget to accept for karma.