Create Charts & Graphs From a Data Table

Overview

Data tables in Qvinci are used to store variables that are used to create a chart or a graph. They are the non-graphical representations of what the graph is to produce. 

Qvinci recommends using a data table to build charts and graphs any time you need to display information that has not synced to Qvinci's database.

Also, if you must use formulas or otherwise need to calculate the values to be used as data points - for example, if your data point involves both Food Costs and Food Sales - it is also recommended to first build a data table, then Insert Chart (this is similar to building a chart in Excel).

If all of the data points for your chart come from either specific financial accounts (like Owner Wages) or the total sections of a report (Total G&A Expense, Total Income, etc.), then it is recommended to use the Chart Chooser to build your chart or graph.

Creating Charts & Graphs from a Data Table

As an example, we'll build a chart that displays a Food Margin Percentage, trending over the past six months.

Step 1. Create a Data Table

A. Determine the date range(s) you want to display in your graph. For each time frame you want to display in your chart, you will need a column in your data table. For example, a "Rolling 6 Month" chart that displays trend data would need 6 columns, 1 column for each month in the account.

B. Use the Column Chooser to create the column(s) for your date range(s).

For the Rolling 6 Month example, we'd need 6 columns. We need to see each month separately; we cannot use the date ranges such as Last 6 Months. We need to specify each month individually, by selecting a column chooser that gives us Last Month, Last Month with an offset of 1-month, Last Month with an offset of 2 months, etc. for all of our columns.

The date ranges should go in the order you'd like to see them in the final chart. If you'd like to see your older date ranges go first, those should be on the left, and the most recent date ranges should go on the right side of the table.

C. Code any financial accounts or calculations into rows in a column to the left.

When hard-coding accounts into a template, your account names in the template must be an exact match with the names listed in your Chart of Accounts. Incorrect spelling, spacing, casing, and punctuation will all prevent the correct data from appearing.

D. If you would like to use the total for an entire section of your chart of accounts, you need to code "Total [name of section]" - i.e., if you had a parent account named "Payroll Taxes" and you wanted the total for that entire section, you'd code "Total Payroll Taxes" in this column.

Our Food Margin involves the accounts "Food Sales" and "Food Costs," so we'll code both into the template.

Then we'll code a "Food Margin" line and use an Excel formula to divide those two accounts.

TIP: When using Excel formulas, it is advised to use the IFERROR function so that only numbers or blank cells appear in your table. [IFERROR(value, value_if_error)], You can use the IFERROR function to trap and handle errors in a formula. IFERROR returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

E. Hide any rows that don't need to appear in your chart.

We don't need to see Food Sales and Food Costs - those are listed only so we can calculate the margin - so we can hide Rows 20-21 that include those values.

F. Apply appropriate cell formatting for data points.

When you add a row, the editor auto-applies Excel's "General" format; however, that is often not the format you want to use for a financial number or percentage. In this case, select the cells with your formulas and select the "Percentage" cell format.

Step 2. Create a Chart or Graph from Data Table

A. Use your mouse to select the entire data table.

B. From the INSERT tab across the top of the editor, click the Chart icon

C. Just like when using Excel's chart wizard, follow the prompts for the type of chart/graph to produce.

Step 3. Creating a New Graph with Chart Chooser

A. If your chart or graph will include accounts that are specific to a Company or Client, be sure to navigate to that Company or Client in the top left before navigating to the Report Editor.

B. Navigate to Reports & Libraries > (New) Libraries to open up a template. 

C. In the top left, click the  Chart Chooser icon.  

D. Choose the data set for your chart or graph.

• • Frequency: This determines how your data set will be grouped in the chart or graph. "By Entity" will split your data set so that each Entity can be viewed as a piece of the whole, rather than splitting the data set into date ranges. The other options will split your data set into the corresponding time periods.
  • Date Range: Select one of our rolling date ranges from the dropdown.
  • Offset Date Range: This option can help you modify the date range. For example, if you want a 2 Months Ago chart, if you haven't closed last month's books yet, the date range "Last Month" "Offset by 1 month" will show you the completed data for 2 months ago.
  • Series: Search for and choose the specific account(s) to be included in your chart or graph.
    • The search will only display accounts for the specific Company or Client data set you chose before opening the Report Editor.
    • You can choose individual accounts, an entire account type (Total Income), or a subset of accounts (Total General & Admin Expense).
      
      

E. Click the green Next button in the top right.

F. Choose the type and sub-type of the chart or graph you'd like to build and click the green Next button at the top right.

• • • NOTE: The preview on the right shows only an example of that chart or graph type, rather than a true representation of your data set.

G. Set the name and size of your chart's title, then click the green Finish button at the top right.

• • • • TIP: "By Entity" charts or graphs do not have the date range in the legend, so you may want to include a date range in the title of "By Entity" charts if the date range isn't labeled somewhere else in your template.

Step 4. Editing an Existing Graph with Chart Chooser

Like Excel's Chart Wizard, the Chart Chooser is only used to set the basic settings for your chart and is not used to customize individual chart elements. If you need to modify your data set or the chart type, though, the Chart Chooser does come in quite handy.

A. Click on the graph to edit.

B. Click the  Chart Chooser button at the top left. This will bring you to the first page where your dataset can be modified.

C. On the left side, you can modify the Frequency and Date Range (see step 4 above) as needed. On the right side, you can either add new financial accounts by searching for them in the Series text box or remove them by clicking the red X to the left of the existing data Series. Once your accounts are set correctly, click the green Next button in the top right.

D. Change the chart type and sub-type if needed, then click Next.

E. Edit the name or font settings for your chart title, then click Finish.

Step 5. Format Chart with the Ribbon's Design Tab

 CHANGE CHART TYPE: Want to see the data represented in a different way? Switch between grouped column charts, stacked area charts, doughnut pie charts, and more here.

 QUICK LAYOUT: Complex charts may need more layout elements to make sense, while simple graphs may not. Quick Layout changes which elements are present and where they appear.

CHANGE CHART STYLE: Different styles modify things like Legend placement, whether there's a background fill, and more. This is more of an overall theme, and things like colors can be changed afterward.

 CHANGE COLORS: This allows you to set an overall color scheme. You can still modify the color of individual elements if you'd like afterward.

 ADD CHART ELEMENT: If your Quick Layout or Chart Style is missing an element you need, like an axis label, or if you'd like to add something extra like actual dollar amounts, this is the place to go.

 SELECT DATA: If you need to add or remove accounts from your chart, it's easier to edit your data set from the Chart Chooser. However, this tab does let you re-order how your accounts appear.

Step 6. Format Individual Chart Elements Using the Format Task Pane

The Format Task pane displays some basic options depending on which part of the chart was selected. Note that not every option will pop up for every element.

TITLE: You can edit the title of the graph, as well as font and size.

CHART AREA:  Fill will change the background color of the chart,  Size will resize your chart, and  Text Fill will change the font color.

LEGEND:  You can choose whether your Legend is displayed above, below, or to the side of your chart.

Step 7. Saving Your Chart/Graph

When your edits are complete, a base version of your chart or graph will be added to your template. To see what the final product will look like, you'll need to click either  Save or  Save As, then click  Preview. 

Note About Special Characters in the File Name

When naming the file, avoid using special characters: "<, >, \, {, and }" which will produce an error until those values are removed.

Have Questions?
We're More Than Happy to Help

Schedule a call with Customer Success below, email us at support@qvinci.com or call us at 1-512-637-7337 Ext. 1 Available M-F, 7:30am-6:30pm CT and it is always FREE!

Schedule an Appointment with Customer Success