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
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
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
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
--
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-users+unsubscribe@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
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
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating pkeating@dimagi.com wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating pkeating@dimagi.com wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating pkeating@dimagi.com wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman carolholtzman@gmail.com wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating pkeating@dimagi.com wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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
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
On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman carolholtzman@gmail.com wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman <carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating pkeating@dimagi.com wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@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
Thanks Patrick!
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,
Patrick2015-06-04 12:49 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman carolholtzman@gmail.com wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating pkeating@dimagi.com wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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...
Thanks Patrick!
On Thu, Jun 4, 2015 at 3:58 PM, Patrick Keating pkeating@dimagi.com wrote:
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,
Patrick2015-06-04 12:49 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman <carolholtzman@gmail.com wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating <pkeating@dimagi.com wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman carolholtzman@gmail.com
: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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Hi Carol,
A couple of questions:
Patrick
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...
On Thu, Jun 4, 2015 at 4:27 PM, Carol Holtzman carolholtzman@gmail.com wrote:
Thanks Patrick!
On Thu, Jun 4, 2015 at 3:58 PM, Patrick Keating pkeating@dimagi.com wrote:
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,
Patrick2015-06-04 12:49 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman <carolholtzman@gmail.com wrote:
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating < pkeating@dimagi.com> wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman <carolholtzman@gmail.com
:
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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@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
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
Hi Carol,
A couple of questions:
- Do the new data columns disappear or are they replaced by other
data?- Are you now seeing your date values in the format you want?
- Regarding the pivot tables, you can change the data source for the
pivot tables to reflect the new data source
- Click on pivot table-->Pivot table tools (appears)--> Analyse-->
Change data sourcePatrick
2015-06-05 9:03 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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...
On Thu, Jun 4, 2015 at 4:27 PM, Carol Holtzman carolholtzman@gmail.com wrote:
Thanks Patrick!
On Thu, Jun 4, 2015 at 3:58 PM, Patrick Keating pkeating@dimagi.com wrote:
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,
Patrick2015-06-04 12:49 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating < pkeating@dimagi.com> wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman <
carolholtzman@gmail.com>: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
--
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-users+unsubscribe@googlegroups.com
.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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?
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:
In summary, you would have* 2 extra columns and would need to change the
pivot table data source to your newly modified data *source.
Thanks,
Patrick
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
On Fri, Jun 5, 2015 at 11:11 AM, Patrick Keating pkeating@dimagi.com wrote:
Hi Carol,
A couple of questions:
- Do the new data columns disappear or are they replaced by other
data?- Are you now seeing your date values in the format you want?
- Regarding the pivot tables, you can change the data source for the
pivot tables to reflect the new data source
- Click on pivot table-->Pivot table tools (appears)-->
Analyse--> Change data sourcePatrick
2015-06-05 9:03 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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...
On Thu, Jun 4, 2015 at 4:27 PM, Carol Holtzman carolholtzman@gmail.com wrote:
Thanks Patrick!
On Thu, Jun 4, 2015 at 3:58 PM, Patrick Keating pkeating@dimagi.com wrote:
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,
Patrick2015-06-04 12:49 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the problem.
Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating < pkeating@dimagi.com> wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman <
carolholtzman@gmail.com>: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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@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
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
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:
- Uncheck the box "Automatically convert dates for Excel" on your custom
export and save the export- Refresh your data, you will now have the dates back in the format with
date and time combined- Add a new column next to your raw data
- 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- You can format this new date by creating a new column and using the
formula =DATEVALUE(Y2) where Y is the column created in step2In 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,
Patrick2015-06-05 9:55 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
On Fri, Jun 5, 2015 at 11:11 AM, Patrick Keating pkeating@dimagi.com wrote:
Hi Carol,
A couple of questions:
- Do the new data columns disappear or are they replaced by other
data?- Are you now seeing your date values in the format you want?
- Regarding the pivot tables, you can change the data source for
the pivot tables to reflect the new data source
- Click on pivot table-->Pivot table tools (appears)-->
Analyse--> Change data sourcePatrick
2015-06-05 9:03 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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...
On Thu, Jun 4, 2015 at 4:27 PM, Carol Holtzman <carolholtzman@gmail.com wrote:
Thanks Patrick!
On Thu, Jun 4, 2015 at 3:58 PM, Patrick Keating pkeating@dimagi.com wrote:
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,
Patrick2015-06-04 12:49 GMT+00:00 Carol Holtzman carolholtzman@gmail.com:
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
On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
Hi NIck,
I filtered them out in the pivot table, but still have the
problem.Carol
On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle nnestle@dimagi.com wrote:
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 6081On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman < carolholtzman@gmail.com> wrote:
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
On Thu, Jun 4, 2015 at 1:21 PM, Patrick Keating < pkeating@dimagi.com> wrote:
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
- Select your column of unorganised dates in your pivot table
- Data tab--->Text to columns-->Next-->Next
- 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:
RedirectingHope it helps,
Patrick2015-06-04 11:07 GMT+00:00 Carol Holtzman <
carolholtzman@gmail.com>: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
--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com
.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.--
Patrick KeatingField 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--
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-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
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