Grouping dates into months for Excel Dashboards

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

  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

··· 2015-06-04 11:07 GMT+00:00 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


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
http://www.dimagi.com

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 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

  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

2015-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 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


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

··· On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman 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

  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

2015-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 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


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

··· On Thu, Jun 4, 2015 at 2:12 PM, Nick Nestle 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 6081

On 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

  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

2015-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 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


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

··· On Thu, Jun 4, 2015 at 2:14 PM, Carol Holtzman 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 6081

On 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

  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

2015-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 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


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

··· On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman 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 6081

On 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

  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

2015-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 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


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

··· 2015-06-04 12:49 GMT+00:00 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

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 6081

On 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

  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

2015-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 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


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
http://www.dimagi.com

Thanks Patrick!

··· On Thu, Jun 4, 2015 at 3:58 PM, Patrick Keating 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,
Patrick

2015-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 6081

On 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

  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

2015-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 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


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
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…

··· On Thu, Jun 4, 2015 at 4:27 PM, Carol Holtzman 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,
Patrick

2015-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 6081

On 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

  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

2015-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 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


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
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:

  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

··· 2015-06-05 9:03 GMT+00:00 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…

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,
Patrick

2015-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 6081

On 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

  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

2015-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 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


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
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
http://www.dimagi.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 wrote:

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

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,
Patrick

2015-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 6081

On 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

  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

2015-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 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


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
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
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?

  • 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

··· 2015-06-05 9:55 GMT+00:00 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

On Fri, Jun 5, 2015 at 11:11 AM, Patrick Keating pkeating@dimagi.com wrote:

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

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,
Patrick

2015-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 6081

On 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

  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

2015-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 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


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
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
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
http://www.dimagi.com

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

··· On Fri, Jun 5, 2015 at 1:05 PM, Patrick Keating wrote:

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

2015-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:

  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

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,
Patrick

2015-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 6081

On 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

  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

2015-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 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


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
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
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
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

··· On Friday, June 5, 2015 at 8:33:55 AM UTC-4, Carol Holtzman wrote: > > 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 > > > > > On Fri, Jun 5, 2015 at 1:05 PM, Patrick Keating <pkea...@dimagi.com > wrote: > >> 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 >> >> >> >> >> 2015-06-05 9:55 GMT+00:00 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 >>> >>> On Fri, Jun 5, 2015 at 11:11 AM, Patrick Keating <pkea...@dimagi.com > wrote: >>> >>>> 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 >>>> >>>> 2015-06-05 9:03 GMT+00:00 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... >>>>> >>>>> On Thu, Jun 4, 2015 at 4:27 PM, Carol Holtzman <carolh...@gmail.com > wrote: >>>>> >>>>>> Thanks Patrick! >>>>>> >>>>>> On Thu, Jun 4, 2015 at 3:58 PM, Patrick Keating <pkea...@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, >>>>>>> Patrick >>>>>>> >>>>>>> >>>>>>> >>>>>>> 2015-06-04 12:49 GMT+00:00 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 >>>>>>>> >>>>>>>> On Thu, Jun 4, 2015 at 2:47 PM, Carol Holtzman <carolh...@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 < carolh...@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 <nne...@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 6081 >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Thu, Jun 4, 2015 at 1:57 PM, Carol Holtzman < carolh...@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 < pkea...@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* >>>>>>>>>>>>> 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 >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> 2015-06-04 11:07 GMT+00:00 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 >>>>>>>>>>>>>> >>>>>>>>>>>>>> -- >>>>>>>>>>>>>> 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 >>>>>>>>>>>>> >>>>>>>>>>>>> -- >>>>>>>>>>>>> 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 . >>>>>>>>>>>>> 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-user...@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-user...@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-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 >>>>>>> >>>>>>> -- >>>>>>> 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 . >>>>>>> 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-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 >>>> >>>> -- >>>> 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 . >>>> 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-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 >> >> -- >> 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 . >> For more options, visit https://groups.google.com/d/optout. >> > >