How do I build a chart widget?

Jeff Nelson

I have a dashboard and when attempting to add a chart widget, I'm told the Data is not able to be charted. I'm using a report with a system generated column with dates. I am looking to chart volume of rows created by creation date (month over month).

Heather Duff

Hi Jeff,

You will probably need to create a sheet (or rows at the top of your sheet) that pulls the numbers you will need. You can create countifs formulas to calculate how many times the dates listed are in a specific year and month. For example:

Let's say I create a summary sheet. I've got three columns in it: Year, Month, Count. I will also use a cross-sheet reference and name the Dates column from the source data as {dates}.

In the Year column of the summary sheet, I will enter the year in 12 rows, then enter 1-12 in the Months column. I will do that for each of the years I want to count.

In the Count column, I will enter the following formula:

=COUNTIFS({Dates}, IFERROR(MONTH(@cell), 0) = Month@row, {Dates}, IFERROR(YEAR(@cell), 0) = Year@row)

To translate, this says: Count the number of times both of these are true: A date in the source sheet is in the month corresponding with the number in your month column, AND a date in the source sheet is in the year listed in your year column.

Hope this helps!

Best,

Heather

Heather Duff

Hi Jeff,

You will probably need to create a sheet (or rows at the top of your sheet) that pulls the numbers you will need. You can create countifs formulas to calculate how many times the dates listed are in a specific year and month. For example:

Let's say I create a summary sheet. I've got three columns in it: Year, Month, Count. I will also use a cross-sheet reference and name the Dates column from the source data as {dates}.

In the Year column of the summary sheet, I will enter the year in 12 rows, then enter 1-12 in the Months column. I will do that for each of the years I want to count.

In the Count column, I will enter the following formula:

=COUNTIFS({Dates}, IFERROR(MONTH(@cell), 0) = Month@row, {Dates}, IFERROR(YEAR(@cell), 0) = Year@row)

To translate, this says: Count the number of times both of these are true: A date in the source sheet is in the month corresponding with the number in your month column, AND a date in the source sheet is in the year listed in your year column.

Hope this helps!

Best,

Heather

Jeff Nelson

Worked like a charm! Thanks, Heather.