Monthly Sales Line graph

[AgentsSka]

It must be the season... but my brain is fried and I'm not seeing how to create this line chart.

 

I have a database with two years of orders in it.

 

I'd like to create a line graph that shows the number of orders/month.  One line per year.

 

What calculations do I need to create, and what kind of self-joins might I need to create such a table?

[dansmith65]

I would use a script to compile the data with Fast Summaries method, store it in return-delimited global variables, then use those variables as the data source for the graph.

 

If you only wanted one year at a time it wouldn't be too difficult to use calculated fields/relationships to get the data, but since you want a series for each year, the scripted method will give you the flexibility you need without having to create a bunch of extra fields/TO's.

[comment]

I have a database with two years of orders in it.

 

I'd like to create a line graph that shows the number of orders/month.  One line per year.

 

It depends on what do you want to do when your data grows to include three or more years. Will the chart always compare two years?

[AgentsSka]

It depends on what do you want to do when your data grows to include three or more years. Will the chart always compare two years?

 

No, I'd like the chart to grow with the data.

[comment]

Well, that's kind of problematic, because the chart setup requires an explicit expression for each series (line). IOW, you can preset the chart for say 10 years, but you cannot make the chart automatically add a line for the 11th year (not even through a scripted process).

[AgentsSka]

Well, that's kind of problematic, because the chart setup requires an explicit expression for each series (line). IOW, you can preset the chart for say 10 years, but you cannot make the chart automatically add a line for the 11th year (not even through a scripted process).

Hmm... Ok, so I'd have to update the chart each New Year.

[dansmith65]

Hmm... Ok, so I'd have to update the chart each New Year.

 

Or revise your process... If you continue down this road, imagine what your chart will look like in 30 years.

[AgentsSka]

Or revise your process... If you continue down this road, imagine what your chart will look like in 30 years.

Ok.  I see your point.  However, I was never intending on having 30 years of data on one chart.

 

Let's say, I'd like to max it at three years.  And, for arguments sake, say that the data was 2010, 2011, & 2012.  Once we get into 2013, 2010 would drop off and 2013 would replace it on the chart.

 

I'm still not sure the best way to organize the data.

[comment]

Let's say, I'd like to max it at three years. 

 

That would be quite different. See the attached for an idea how it could be done. it's not the only way, but it is a rather straightforward one, IMHO.

ComparisonChartSlots.fp7.zip

[AgentsSka]

Thanks.  This does seem pretty straight forward.

 

I've set up a portal similar to what you have created and the info displays properly.

 

However, the chart does not display correctly.  And, it takes forever to load before displaying this:

As expected, the calculations that are in for the Y axis return the following:

 

Series 1 - LeftValues ( List ( Orders per Month::cCount ) ; 12 )

 

257

167

138

144

166

113

79

211

265

955

4425

5463

 

 

Series 2 - MiddleValues ( List ( Orders per Month::cCount ) ; 13 ; 12 )

 

426

276

323

333

419

363

283

414

517

1199

5230

4975

 

While trouble shooting, I even dropped series 3 from the chart because there is no data for those dates yet.  That still didn't fix the issue.  The results are similar to the above image.

 

Any idea why the chart would be failing?

 

[comment]

There are many possible reasons. You need to go over the settings and compare yours to mine. Check especially the X-axis definition and the setting for 'Use data from: [ ... ]'.

[AgentsSka]

Got it.  

 

Awesome!  Thanks for the help.