Preparing Data for Visualization

Tech Tip Provides Guidance for using Discover & Visualization

While the tools are very easy to use, you may need to prepare the data to produce more complex graphs or charts. For example, suppose you have an IDEA database with this year’s sales in it and you have the same file but with last year’s sales in it. Same fields, different years. You can easily append the files and see how sales are doing over time using the line chart in Visualization. You’ll get something like this.

Figure 1 – Straight Line Sales Trend by Month

But a “this month over the same time last year” representation of the same data might tell a more meaningful story to a sales or marketing executive. Something like this.

Figure 2 – Sales by Month, Current Year vs Previous Year

How to Create a Multi-Series Chart:

1. Create a grouping field.
   1. The IDEA database files used in this example had no way to group the data by month. A field was appended to each file titled, “Month.”
   2. This equation was used to create the data in the field:
      1. @month( INV_DATE )
2. Join the files
   1. The two files were joined using the newly created “Month” field as the match field.
3. Rename the previous year’s sales amounts to make identification easier
   1. The “SALES_PLUS_TAX” field for the prior year was renamed to “PREVIOUS_YEARS_SALES_PLUS_TAX”.
4. Open Visualization
5. Create a new dashboard
6. Create a line chart using these criteria:
   1. Chart type: Line Chart
   2. Database: the joined result set
   3. Group by: the newly created “Month” field
      1. Leave “Stratify” and “Exclude zeros/errors/blanks” blank
   4. Statistic: Sum
   5. Field to Sum:
      1. Fields to sum are the Y-series fields
      2. You can select up to five fields (series)
         1. In this example, the fields SALES_PLUS_TAX and PREVIOUS_YEARS_SALES_PLUS_TAX (the renamed field) were selected
   6. Chart Caption: Title the chart as desired
   7. X-axis Title:  “Month” was used in this example
   8. Y-axis Title:  “Sales Yr over Yr” was used in this example
   9. Show Legend: This was checked to show what data was displayed in each line color
      1. This is the result:
7. Click Save

If you have questions about this or other IDEA functions, please contact our help desk:
888-641-2800 Option 4
[email protected]