Complex Charting needs

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I am not too sure if this is beyond the capability of QB. I need to compare a store sale to state average and national by dept. I can create some simple chart but cannot figure how to add average values to the chart series. I have attached a sample I created in MS Access. Will appreciate some help. 

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
The National Average and the State Average need to simply be fields on the Project record.  To get those fields, you will need to have a parent record for the Nation, even if it is one record in a single table to act as the Parent record connected to all Stores.  For example a "country" table with 1 Country (Australia), and a formula field on each Store as "Australia".   Then summarize the Average up to that record and then look it up back down to the Store records.

For the State Averages, you will need to have a table of States as Parent to the Stores, and again make a summary average and then look it up down to the stores record.  Typically these averages will only filter in Stores open longer than say 12 months so as not to distort the Averages with new stores open less than a year and hence having not had a full year of sales.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks Mark. I think it is doable but I am really loath to creating a web of tables which in the absence of decent documentation makes it very hard to follow. Any chance I can add a picture of a such a chart (processed in Excel) each month in the dashboard?
i hesitate to tell you how to do this as it will commit you to the endless task of keeping images updated manually.  

You can build a rich text section in your dashboard and there is a way to paste in a jpg image.

But really, you are better off to create the chart once, even with some effort or outsourcing, then to commit to a lifetime of manual updates of images ina Dashboard.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks.  You are right. Will do it.