how to display graph for sorted categories

Question

Level 1

8 points

how to display graph for sorted categories

I have a large range of numbers reflecting sales over the past year. I want to see when our peak sales season is?

The sales are broken down into date and number of units per purchase. (it could also be date and net sales it doesn't make a differnece for the results)

I have sorted the list by date using "categorize by this colum". This sorts all of the transactions into groups by their day. I can then select the unit (or net total) and have it display "subtotal"

Again this looks good in the table…Now I want to create a bar chart. I minimize the row so that only the days total is displayed and then I select these two columns and click Chart. The chart I get is empty.

If I expand the dates to display all the transactions in the table I get a bar chart with several transactions instead of one day total.

I really don't want to have to create a new colum and sum each day. That would take hours.

Is there a way to get the chart to display… ultimately by day, month and quarter.

Thanks in advance.

iMac 24-inch : 3.06GHz, Mac OS X (10.5.6)

Posted on Dec 8, 2011 11:24 AM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Dec 8, 2011 1:34 PM in response to franclin

"Categories" provide a method of viewing the data and summaries of the data, but those summaries are for viewing only, and can't be used for further calculations or to feed charts.

To gather data for a chart, you'll need to create a Summary table where the data can be summarized, and can be used to supply chartable data.

Here's an example. Click the image for a larger view. Formulas used are listed below the image.

The Summary chart uses two formulas:

B2 and filled down:

=IF(COUNTIF(Data :: $A,A2)>0,COUNTIF(Data :: $A,A2),"")

The 'working part of this is COUNTIF(Data :: $A,A2)

Which counts the number of times each date appears in column A of the table "Data". The surrounding IF part puts a null string ( "" ) into cells where the date count is zero. The null string is a text value, and results in the gap shown for January 4 in the line chart (and for the bar chart, although here a zero value would look the same).

C2 and filled down:

=IF(B2="","",SUMIF(Data :: $A,A2,Data :: C))

The working part here is =SUMIF(Data :: $A,A2,Data :: C)

which sums the values in column C of Data matching each date.

The IF part here suppresses the calculation if the count for that date (in column B) is the null string noted above.

For charts showing monthly or quarterly results, you'll need to add columns to the Data table to calculate the Month: =MONTH(A)

or Quarter: =CHOOSE(MONTH(A),1,1,1,2,2,2,3,3,3,4,4,4) or =1+INT(MONTH(A)/3)

for each date.

(Both quarter formulas assume the quarters start January 1, April 1, July 1 and October 1.)

Modifications to the formulas, or additional columns in the Summary tables may be needed if the Quarters start on dates different from those used, if the Data table extends over more than one fiscal year, or if the calculated values, which must match the condition values in the two formulas, are not suitable for use in labeling the category axis on the charts (which get this value from the header column (A) of their Summary chart.

If you need assistance with those modifications, reply with a mmore detailed specification.

Regards,

Barry

Reply