Grouping dates into months for Excel Dashboards

[Carol Holtzman]

Hello CommCare Community,

Does anyone have experience with creating Excel dashboards? I am trying to create one and when I try to group dates as months instead of days, I get an error message saying that I "Cannot group that selection" (when I right click in the cell with the date as instructed by the tutorial).  It seems like Excel doesn't recognize that date, although I did check the box that says to "Automatically convert dates for Excel".  Any other way around this? 

Thanks.

Carol

[Patrick Keating]

Hi Carol,

One potential cause of this problem is that Excel is not actually recognising your dates as dates.

As a first step, it's good to check that all dates in your column are actual dates and not for example ---. You can filter these non-date values out.

Potential solution

1. Select your column of unorganised dates in your pivot table

2. Data tab--->Text to columns-->Next-->Next

3. Step 3 of that wizard, check "Date" under Column data format, then choose the appropriate format from the droplist, eg: DMY. Click Finish. 

I obtained this information from here: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/cannot-group-data-for-date-fields-in-a-pivot-table/fe380d63-446d-e011-8dfc-68b599b31bf5

Hope it helps,

Patrick

--
You received this message because you are subscribed to the Google Groups "commcare-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to commcare-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Patrick Keating

Field Manager | Gestionnaire sur terrain

West Africa | Afrique de l'Ouest

Dimagi, Inc

Mob Sénégal: +221775716949

Mob Burkina Faso: +22674434915

Skype: patby19

http://www.dimagi.com

[Carol Holtzman]

Hi Patrick,

Thanks for your response.  I tried it and it still didn't work.  Not sure what I'm doing wrong.  In the "raw data" sheet, I filtered out the non-date values out and then went to "text for columns" and followed the instructions.  

Carol

--
You received this message because you are subscribed to a topic in the Google Groups "commcare-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/commcare-users/EBUu28cUYtw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to commcare-user...@googlegroups.com.

[Nick Nestle]

Hi Carol,

Excel also won't recognize the dates if there are non-dates in the column.  Are any of your dates blank or marked as "---"?  Every item in the column must be a propertly formatted date

-Nick

Nick Nestle

Project Manager | Dimagi South Africa

+27 79 439 6081

[Carol Holtzman]

Hi NIck,

I filtered them out in the pivot table, but still have the problem.  

Carol

[Carol Holtzman]

Hi Nick/Patrick,

I decided to experiment and create 3 new columns next to my original date column and pulled out the day, month, and year, in each column.  Then I combined the day, month, and year again as a date.  That new date (which looks exactly like the dates of my original date column) allows me to group!!!! 

Interestingly, when I pull out the month using the function (=month(date)) for the date of 11/30/2013, I get 1/11/1900--I was expecting simply a value of "11".  For the day I got 1/30/1900 (expected would be "30") and for the year I got 7/5/1905 (expected would be "2013").  

I am curious whether others have had issues with the dates in CommCare.  Perhaps this is unique to my data set.  Although I'm not sure why formatting my dates with this strategy works. 

Carol

[Carol Holtzman]

Also, if I place these new columns into my "raw data", will they also automatically update when I refresh it with the updated CommCare data?

Thanks.

Carol

[Patrick Keating]

Hi Carol,

Great to hear you were able to solve your date issue.

Regarding your 2nd email, if you place your 3 new columns adjacent (no empty column between raw data and date columns), then they should automatically update when you refresh the data.

However, this will only be the case if you ticked the box "Fill down formulas in columns adjacent to data", when you created your dashboard (see below)

Thanks,

Patrick

[Carol Holtzman]

Thanks Patrick!

[Carol Holtzman]

So, unfortunately, the new data columns go away after I refresh the data.  

And then my pivot tables are "invalid" because they don't match the columns in my raw data.  

Not sure what I'm doing wrong...

[Patrick Keating]

Hi Carol,

A couple of questions:

1. Do the  new data columns disappear or are they replaced by other data?
2. Are you now seeing your date values in the format you want?
3. Regarding the pivot tables, you can change the data source for the pivot tables to reflect the new data source
1. Click on pivot table-->Pivot table tools (appears)--> Analyse--> Change data source

Patrick

[Carol Holtzman]

Hi Patrick,

The data columns are gone completely.  But my pivot tables still show the data columns (it's just gone from the raw data).  

I tried to create the columns again and get the date values, but for some reason, it's not working today.  That is, even with the new dates I've created, I am unable to group them.

Thanks.

Carol

[Patrick Keating]

Hi Carol,

Can I ask you to check the number of columns you had in your dashboard before you refreshed the data and then how many you had after refreshing?

• I encountered a similar problem before and it was due to adding in case properties to the export and having them replace the new ones I had added manually in Excel.
  

Also, there is another way of handling dates in Excel, but it isn't as clean as the version Cory suggested yesterday.

This is not the ideal method, and we would recommend using the method Cory suggested.

You could do the following:
1. Uncheck the box "Automatically convert dates for Excel" on your custom export and save the export
2. Refresh your data, you will now have the dates back in the format with date and time combined
3. Add a new column next to your raw data
4. Add this formula to the new column =LEFT(X2,(10))   where X2 would be the relevant column with the date value

This will leave you with for example 2014-08-22
5. You can format this new date by creating a new column and using the formula =DATEVALUE(Y2) where Y is the column created in step2

In summary, you would have 2 extra columns and would need to change the pivot table data source to your newly modified data source.

• You may have to refresh the table after changing the data source.
  
• You should then be able to group those dates.
  

Thanks,

Patrick

[Carol Holtzman]

Hi Patrick,

Thanks for all your help!

I had to create 9 new columns in order to get 2 different dates to group in my pivot table.  When I "Refresh All", those columns just disappear before me.  I did count and my columns went from 65 to 56 (I know, lots of columns--I'm just trying to figure out what kind of tables I can get out of my case properties).  

The good news is that the data was being updated.  That is, I added a fake patient and it was added. 

I also figured out why I couldn't get my dates to format the second time.  Turns out I need to filter out the blank date cells FIRST, and then create those new columns (it won't work if you filter them out at the end).  Cory's method worked fine too with the CommCare date and time--I was able to group that date in my pivot table.  I can play around with the alternative method you gave me...

And now I just need to figure out how to keep those columns when the data refresh...

Carol

[Jenn Masters Snyder]

Hi all,

I'm having this same issue discussed here, back in 2015. I've tried the solutions discussed here but none of them are quite working. Was a solution ever found? I can't seem to find anything in any of the documentation.

Thank you!

Jenn