topic Re: How to create serial chart displaying month and year only in ArcGIS Dashboards Questions

How to create serial chart displaying month and year only

RTAAdmin — Wed, 07 Apr 2021 13:33:36 GMT

Hi,

I have monthly data that I want to show in operations dashboard for multiple years. The dashboard hands the monthly data just fine for a single year, but I cannot figure out how to format my csv file to have it display monthly data for 2020 and 2021.

I opened a case with ESRI on this and haven't gotten anywhere in a few weeks.

The only work around I have found is adding a day to the month and year, but this is not accurately representing the monthly data.

Any help would be appreciated!

Re: How to create serial chart displaying month and year only

jcarlson — Wed, 07 Apr 2021 13:40:41 GMT

Unfortunately, the Dashboard is expecting dates to be full timestamps, so month/year data without the day often has issues like this. Do you have multiple rows per month? Or is each row in the CSV a separate month?

Re: How to create serial chart displaying month and year only

RTAAdmin — Wed, 07 Apr 2021 13:55:59 GMT

Thanks for the response. Each row is a separate month.

Re: How to create serial chart displaying month and year only

RTAAdmin — Wed, 07 Apr 2021 14:06:47 GMT

Any possible workarounds?

Re: How to create serial chart displaying month and year only

jcarlson — Wed, 07 Apr 2021 14:46:28 GMT

So, your options are sort of limited. A chart set to Grouped Values or Features can Parse Dates.

But this requires your date field to be in the specific patterns available. You could alter the CSV to display yyyyMM values and this would work. Then set the chat's Minimum Period to Month.

Otherwise, you'd need some way to reliably sort your values as a string or a number. Reformatting your date field could work for this too, but as it is currently in your CSV, you'll get the Aprils at the top regardless of year, etc.

Re: How to create serial chart displaying month and year only

RTAAdmin — Wed, 07 Apr 2021 16:44:53 GMT

Thanks Josh! I will try this and report back.

Re: How to create serial chart displaying month and year only

RTAAdmin — Wed, 07 Apr 2021 19:41:27 GMT

I reformatted my fields to YYYYMM in the csv (attached) and set the chat's Minimum Period to Month. But still not having any luck - getting a weird result. Thanks again for your help on this....

Re: How to create serial chart displaying month and year only

jcarlson — Wed, 07 Apr 2021 19:59:03 GMT

Weird... Is that "Grouped Values" or "Features"? Features is the way to go with one row per month. Then you can set the Series to be your other columns, instead of getting the count of features in the "group".

Given that some of your values seem to be getting tossed over to 1970, I'd guess that your year/month field is being interpreted as an integer? Underneath the formatting, a timestamp is really just an integer, and a timestamp of, say 202001, comes to to January 3, 1970.

I'd guess that your CSV values are being interpreted as integers, which themselves can be directly converted to timestamps, so it's probably parsing them that way. Can you "force" the field to be interpreted as a string? Or create a new string field and copy the values as text?

It seems even stranger that at least one of your values is correctly 'landing' in 2020, but the others don't.

Re: How to create serial chart displaying month and year only

jcarlson — Wed, 07 Apr 2021 20:03:08 GMT

Also! I was working with the Beta Dashboards, but in the standard Dashboard interface, the parsing options are a little better.

Try using YYYY/MM so that the "/" character keeps your CSV data as text, rather than casting it to integers.

Re: How to create serial chart displaying month and year only

RTAAdmin — Wed, 07 Apr 2021 20:21:04 GMT

Grouped values results in only one month of data and only shows the month - not month and year.

Features works but if the date field is formatted as string, it wont convert it to month and year.

Seems like a simple thing to do!

Re: How to create serial chart displaying month and year only

jcarlson — Wed, 07 Apr 2021 21:15:03 GMT

Well, I took the CSV you shared and uploaded it, then published as a hosted feature layer. Letting it keep the date field as a "date" type appears to infer the dates as being in MMM-DD format, defaulting them all to this year.

On a second attempt, I told the import tool that it was a "string" field, but I didn't alter the formatting. Here's my serial chart:

Without applying a sort, it takes the features in objectID order, so as long as the CSV has them in order, it comes out okay.

Here's a field I added, Date 2, in which the dates have been re-written as YYYY-MM:

And here's the serial chart based on that field, using Parse Dates:

EDIT: Meant to add, your CSV has several empty rows at the end, so that could also be why you're seeing values way over in 1970. I'd left the empty rows in on the first upload, and I was getting a wonky chart just like you posted earlier. Removing them prior to upload helped.

Re: How to create serial chart displaying month and year only

RTAAdmin — Thu, 08 Apr 2021 21:59:02 GMT

THANK YOU!

I really owe you one. If we ever meet in person, I owe you a drink. Thanks so much for this - you solved a problem that ESRI tech support hasn't been able to resolve for weeks.

Re: How to create serial chart displaying month and year only

jcarlson — Thu, 08 Apr 2021 23:19:55 GMT

You're welcome! And you just might, as we appear to be in the same region. Metra might even be coming to Kendall County one of these days!