Skip to main content
Solved

Issue sorting by month number in a simple YTD dashboard chart

kevinpopeck
Freshman II
Forum|alt.badge.img

I’m trying to create a simple Year-to-date chart on a dashboard to compare 2 different series of data and am experiencing an issue when sorting on month number.

I would like the data to display in correct month order (i.e. 1,2,3,4,5,6…) and instead it seems to be sorting differently (i.e. 1,10,11,12,2,3,4,5...). I’ve tried multiple Sort By options on the chart settings and nothing seems to change. Is the sorting issue because I don’t have a full year’s worth of data in both series? Are my sort settings wrong? Am I representing the month number in the GI the correct way? Any help would be appreciated. Thanks!

GI Datafield
GI Data
Chart Settings
Results

 

Best answer by craig2

Hi @kevinpopeck ,

Not sure if this is the best practice or not, but I’ve worked through the same issue by adding a “0” to the front of the single-digit month numbers.  And to do that, you have to convert the date to a string type.  So, something like this:

=IIf(Len(CStr(Month([ARInvoice.DocDate])))=1,Concat('0',CStr(Month([ARInvoice.DocDate]))),CStr(Month([ARInvoice.DocDate])))

That should then allow you to sort Ascending to achieve proper month order.  Hope it helps!

View original
    Did this topic help you find an answer to your question?

    2 replies

    craig2
    Pro I
    Forum|alt.badge.img+3
    • craig2
    • Pro I
    • 97 replies
    • Answer
    • January 11, 2024

    Hi @kevinpopeck ,

    Not sure if this is the best practice or not, but I’ve worked through the same issue by adding a “0” to the front of the single-digit month numbers.  And to do that, you have to convert the date to a string type.  So, something like this:

    =IIf(Len(CStr(Month([ARInvoice.DocDate])))=1,Concat('0',CStr(Month([ARInvoice.DocDate]))),CStr(Month([ARInvoice.DocDate])))

    That should then allow you to sort Ascending to achieve proper month order.  Hope it helps!

    Chris Hackett
    kevinpopeck
    2 people like this
    • Chris Hackett
      Chris Hackett
    • kevinpopeck
      kevinpopeck
    kevinpopeck
    Freshman II
    Forum|alt.badge.img
    • kevinpopeck
    • Author
    • Freshman II
    • 12 replies
    • January 11, 2024
    craig2 wrote:

    Hi @kevinpopeck ,

    Not sure if this is the best practice or not, but I’ve worked through the same issue by adding a “0” to the front of the single-digit month numbers.  And to do that, you have to convert the date to a string type.  So, something like this:

    =IIf(Len(CStr(Month([ARInvoice.DocDate])))=1,Concat('0',CStr(Month([ARInvoice.DocDate]))),CStr(Month([ARInvoice.DocDate])))

    That should then allow you to sort Ascending to achieve proper month order.  Hope it helps!

    Your suggestion worked like a charm! Thank you so much for your help @craig2.

    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett

    Reply


    Most helpful members this week

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    About Acumatica ERP system
    Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
    © 2008 — 2024  Acumatica, Inc. All rights reserved