topic Month-Day sort using numeric representation in App Development

Month-Day sort using numeric representation

prayner — Thu, 28 Sep 2023 13:05:46 GMT

I created a date format called [MonthDay] using Auto Calendar:

Dual(Month($1)&'-'&Day($1), monthstart($1)) AS [MonthDay] Tagged ('$axis', '$monthday', '$qualified'),

My plan is to create a drill-down master item where the users drill down Month > Week Number > MonthDay for planting dates.

Currently, my approach successfully extracts the month-day from each planting date and the drill down will successfully sort the month and week number generated by Auto Calendar (see screenshots).

The problem is that the Month-Day field is still sorted by the actual date including the year value. So Nov-13 will appear after Nov-20 because Nov-13 was extracted from 11-13-2023 while Nov-20 was extracted from 11-20-2019.

How can I sort the Month-Day field using a numeric representation ranging from 1-365?

Screenshots for visualization:

date.Autocalendar.month: Successfully sorted

date.Autocalendar.week: Successfully sorted

Month-Day: Unsuccessfully sorted

Re: Month-Day sort using numeric representation

Or — Thu, 28 Sep 2023 13:31:17 GMT

MonthStart() pulls the full date of the start of the date's month. It sounds like you just want the Month(Date), or possibly something like Month(Date)*100 + Day(Date)?

Re: Month-Day sort using numeric representation

prayner — Thu, 28 Sep 2023 15:07:42 GMT

Thank you for the prompt response Or! If I understand correctly, i am creating a field where Nov-13 would have a numeric representation of 1,113.

I replaced the previous line in my post with:

Dual(Month($1)&'-'&Day($1), Month($1)*100 + Day($1)) AS [MonthDay] Tagged ('$axis', '$monthday', '$qualified'),

Here is the output when I enter planting_date_md.autoCalendar.MonthDay as the x-axis dimension and =[planting_date_md.autoCalendar.MonthDay] as the sorting expression in Ascending order. The order is correct, values aggregated across years, and there are no duplicate dates:

However, when I enter the Drill Down Dimension (autocalendars month, week, monthday) as the x-axis dimension, dates are out-of-order. I am using =[planting_date_md.autoCalendar.MonthDay] as the sorting variable in Ascending order. When this chart is converted to a table, values are ordered by the y-axis value...

Not sure how to sort a drill down dimension.

Re: Month-Day sort using numeric representation

prayner — Fri, 29 Sep 2023 07:57:47 GMT

Converting the boxplot to a table, the table is always sorting by Month, no matter what I enter in the sorting properties. Of course, in a table i can just click on the MonthDay column, but in a boxplot I don't have that option.

Re: Month-Day sort using numeric representation

prayner — Fri, 29 Sep 2023 08:46:36 GMT

OK, the issue is related to the week variable. After removing it, I don't get the same issue. When I remove Month, week variable is still sorting out of order. This kinda makes sense because MMM-DD is not consistently in the same week number for each year. Need to think of a workaround.