Audimation Services has been acquired by Caseware International Learn More.


Preparing Data for Visualization

Tech Tip Provides Guidance for using Discover & Visualization

A picture is worth a thousand…you know the rest. Images, our shared visual language, can convey information across cultural and national boundaries in an instant. Charts and graphs are the shared visual language of business.

IDEA’s Discover and Visualization tools let you take advantage of that shared visual language, letting you turn thousands of detailed line items into meaningful charts and graphs.

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.

Straight Line Sales Trend by Month
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.

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


How to Create a Multi-Series Chart:


Create Multi-Series Chart Dialog Box

  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: Line Chart Data Legend
  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]

CaseWare IDEA , Tech Tip

Posted By

By Team Audimation

Related Posts
Reusing Import Templates
Jan 12 Report Reader is a powerful tool for bringing PDF or print reports into IDEA. However importing the same report month after month can get time consuming and ted...
Infusing Python into Your Analysis with IDEA
Oct 06 The tech community often refers to Python as the official programming language for non-programmers. It’s free and easy to learn (even if you don’t know wher...
Expert Data Importing Services Available
Oct 17 To help accounting and audit professionals meet the challenges of acquiring, normalizing and importing client data into IDEA for analysis, Audimation Services, ...

This website has been designed for modern browsers. Please update. Update my browser now