data analysis methodology and conventions n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Data Analysis Methodology and Conventions PowerPoint Presentation
Download Presentation
Data Analysis Methodology and Conventions

Loading in 2 Seconds...

play fullscreen
1 / 88

Data Analysis Methodology and Conventions - PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on

Data Analysis Methodology and Conventions. IMS Consulting Group Training. January 31 2012. What this course is about. We will teach you how to make dynamic analyses with simple user interfaces to automate analyses. Simple user interface to select countries, drugs, therapy areas etc.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Data Analysis Methodology and Conventions' - sydnee-moses


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
data analysis methodology and conventions

Data Analysis Methodology and Conventions

IMS Consulting Group Training

January 31 2012

what this course is about
What this course is about

We will teach you how to make dynamic analyses with simple user interfaces to automate analyses

  • Simple user interface to select countries, drugs, therapy areas etc.
  • Titles and labels update automatically
  • Metrics are calculated instantly from the raw PADDS MIDAS extract
  • Charts are updated automatically

IMS Consulting Group Data Analysis Training - January 17 2012

what this course is about1
What this course is about

More importantly we will teach you good practices to perform fast, accurate and insightful analysis of MIDAS sales data

5 Data Analysis Steps

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

IMS Consulting Group Data Analysis Training - January 17 2012

agenda
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

introductions
Introductions
  • Please say:
    • Your name
    • Your office
    • What you want to learn today

IMS Consulting Group Data Analysis Training - January 17 2012

training objectives
Training objectives

We want to enable our staff to perform common analyses quickly and accurately

  • Improve the speed and efficiency of data analysis, allowing consultants to spend more time on value-added activities
  • Improve accuracy and reduce the need for rework
  • Introduce the good habits of documentation and structure to help reuse, update or revise analyses at a later date
  • Teach a set of standard approaches, so that work can be understood and reviewed more easily by others
  • Give confidence to the attendees that they have the skills to be resourced across the full range of IMSCG projects

IMS Consulting Group Data Analysis Training - January 17 2012

what you will learn
What you will learn

This course is designed to teach practical skills and good practices for sales data analysis

  • A 5-step framework for undertaking MIDAS data analysis
  • Things to look out for to understand sales data for therapy areas, drug classes and countries
  • A recommended approach to pulling and processing MIDAS data
  • New techniques:
    • Use of functions to create dynamic tables
    • How to replicate tables for multiple countries / products
    • Some simple user interface tools (forms)
  • Standards for colours and formats that will improve consistency and readability across tools and projects
  • Good practices to work cleanly, quickly and accurately

IMS Consulting Group Data Analysis Training - January 17 2012

data analysis steps
Data analysis steps

Today’s training is structured around 5 steps that are followed in almost all good MIDAS data analyses

5 Data Analysis Steps

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

IMS Consulting Group Data Analysis Training - January 17 2012

training exercises
Training exercises

During the course of today, we will follow a set of exercises to extract data from PADDS and create analyses and user interface

PADDS

Completed Analysis With User Interface

IMS Consulting Group Data Analysis Training - January 17 2012

project example
Project example

The techniques demonstrated today can be used to create more sophisticated client deliverables

IMS Consulting Group Data Analysis Training - January 17 2012

project example1
Project example

More complex models still use the same principles

IMS Consulting Group Data Analysis Training - January 17 2012

course overview
Course overview

Understanding your therapy area and countries of interest is vital to minimise errors later on in the process

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

  • Learning objectives:
  • How to use MIDAS and other resources to understand the therapy area and countries of interest so that you are equipped to pull the data correctly from PADDS
  • Key activities
  • Review and discuss key questions to ask about the data and the resources used to answer them
  • Quiz based exercise using MIDAS and other resources

IMS Consulting Group Data Analysis Training - January 17 2012

course overview1
Course overview

With good knowledge of the therapy area and countries of interest the data can be correctly pulled from PADDS

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

  • Learning objectives:
  • Best practice for pulling data from PADDS
  • Key activities
  • Review and discuss top tips for pulling data from PADDS

IMS Consulting Group Data Analysis Training - January 17 2012

course overview2
Course overview

Following the extract from PADDS, the data must be processed before it can be analysed

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

  • Learning objectives:
  • Good practice for processing MIDAS data in Excel
  • Formatting conventions to improve consistency across projects
  • Advice for how to work ‘cleanly’ and in a traceable way
  • Key activities
  • Review and discuss top tips and examples of best practice for processing data
  • Practical Excel exercise to process a raw MIDAS extract

IMS Consulting Group Data Analysis Training - January 17 2012

course overview3
Course overview

Dynamic tables are a highly efficient and accurate way to analyse the processed data

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

  • Learning objectives:
  • Practical techniques to create dynamic tables in Excel
  • Understanding of various different applications for dynamic tables and their power to save time and improve accuracy
  • Simple user interface techniques
  • Key activities
  • Review and discuss top tips and examples of best practice for developing dynamic tables data
  • 6 practical Excel exercises of increasing difficulty building dynamic tables and accompanying charts

IMS Consulting Group Data Analysis Training - January 17 2012

pivot tables
Pivot tables

Pivot tables are an alternative way to create dynamic tables and are often a fast and effective analysis tool

due to time constraints pivot tables are not covered in this course

Advantages of pivot tables:

  • Often very quick to set up
  • Eliminates some kinds of errors
  • Automatically eliminates rows for non-existant data*

Disadvantages of pivot tables:

  • Not appropriate for client deliverables as hard to format attractively
  • Not as flexible as setting up a table from scratch
  • Charting forces a PivotChart (unless do a workaround)
  • Long timeframes can be a pain to set up (e.g. 84 months of data)

*depending on settings

IMS Consulting Group Data Analysis Training - January 17 2012

course overview4
Course overview

Once the analysis is complete the it is crucial to check you work for error

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

  • Learning objectives:
  • Gain awareness of the different sorts of error that can occur
  • Lean various approaches for preventing, spotting and correcting the different sorts of error
  • Key activities
  • Review and discuss top tips and examples of best practice for error handling
  • Exercises for spotting and correcting various errors

IMS Consulting Group Data Analysis Training - January 17 2012

agenda1
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

adhd overview
ADHD Overview

There are three different types of ADHD, depending on which symptoms are strongest in the individual

  • It is hard for the individual to organize or finish a task, to pay attention to details, or to follow instructions or conversations.
  • The person is easily distracted or forgets details of daily routines

Predominantly Inattentive Type

  • The person fidgets and talks a lot. It is hard to sit still for long (e.g., for a meal or while doing homework). Smaller children may run, jump or climb constantly.
  • The individual feels restless and has trouble with impulsivity. Someone who is impulsive may interrupt others a lot, grab things from people, or speak at inappropriate times.
  • It is hard for the person to wait their turn or listen to directions. A person with impulsiveness may have more accidents and injuries than others.

Predominantly Hyperactive-Impulsive Type

Combined Type

  • Symptoms of the above two types are equally present in the person.

Source: CDC ADHD Facts

IMS Consulting Group Data Analysis Training - January 17 2012

adhd overview1
ADHD Overview

ADHD is one of the most common neurobehavioral disorders of childhood

Percent of Youth 4-17 ever Diagnosed with Attention-Deficit/Hyperactivity Disorder: National Survey of Children's Health, 2007

  • ADHD is usually first diagnosed in childhood and often lasts into adulthood
  • In Europe the prevalence is lower than the US which may be explained by lower awareness and use of different diagnostic tools

Source: CDC State-based Prevalence Data of ADHD Diagnosis

IMS Consulting Group Data Analysis Training - January 17 2012

adhd overview2
ADHD Overview

Treatment for ADHD is a combination of medical and behavioural therapy

Commonly Prescribed ADHD Medications

IMS Consulting Group Data Analysis Training - January 17 2012

agenda2
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

1 understanding your therapy area countries of interest
1. Understanding your therapy area / countries of interest

Answering these questions will make sure that you pull the data correctly from PADDS

Key therapy area questions

Key country questions

Purpose of the analysis

PADDS Pull & Export

  • Have you included all the attributes that you need (e.g. Molecule List, ATC123 Corporation, Manufacturer, Country, Product, Int Product, Int Strength, Strength, Pack, NFC123, etc.)?
  • Within the attributes, have you selected all of the molecules, countries, forms etc. that you need?
  • Have you included all of the measurements that you need (e.g. Sales, LC Sales, Standard Units, etc.)?
  • Have you selected the correct time units (e.g. QTRs, Months, Years, MATs, etc.) and timeframe?

IMS Consulting Group Data Analysis Training - January 17 2012

1 understanding your therapy area countries of interest1
1. Understanding your therapy area / countries of interest

Unless you've worked in the therapy area before, read up on it before diving in

IMS Consulting Group Data Analysis Training - January 17 2012

1 understanding your therapy area countries of interest2
1. Understanding your therapy area / countries of interest

Some useful resources

  • Disease Primers can often be found on Xchange

IMS Consulting Group Data Analysis Training - January 17 2012

1 understanding your therapy area countries of interest3
1. Understanding your therapy area / countries of interest

Some useful resources

IMS Consulting Group Data Analysis Training - January 17 2012

1 understanding your therapy area countries of interest4
1. Understanding your therapy area / countries of interest

Some useful resources

IMS Consulting Group Data Analysis Training - January 17 2012

1 understanding your therapy area countries of interest5
1. Understanding your therapy area / countries of interest

With a couple of PADDS pulls you can learn a lot

IMS Consulting Group Data Analysis Training - January 17 2012

1 understanding your therapy area countries of interest6
1. Understanding your therapy area / countries of interest

Make sure you are familiar with the data coverage in your countries of interest

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 1
Exercise 1

To familiarise yourself with the therapy area and ensure that your PADDS extraction is appropriate, carry out the following exercise

Exercise 1

  • Consider Brazil, Italy, UK, US only (all panels)
  • Using PADDS, answer the following questions:
  • What drugs (molecules) are available in ATC N6B0 in each country?
      • IF YOU HAVE TIME: which of these should be used in our drug class definition for ADHD? (search the internet – not MIDAS for this task)
  • What formulations are available in each country?
  • What products have generic versions in each country?
  • How many strengths exist for oral methylphenidate in the USA?
    • How do the strengths relate to the formulation?

IMS Consulting Group Data Analysis Training - January 17 2012

agenda3
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

2 pulling data and exporting from padds
2. Pulling data and exporting from PADDS

Some top tips for pulling and exporting data from PADDS

IMS Consulting Group Data Analysis Training - January 17 2012

2 pulling data and exporting from padds1
2. Pulling data and exporting from PADDS
  • Pull “everything” at once
  • Be sure to remove “all others” for global sales estimates

IMS Consulting Group Data Analysis Training - January 17 2012

2 pulling data and exporting from padds2
2. Pulling data and exporting from PADDS
  • Switch Layout to Absolute
  • Pull “Local Currency” Euro/USD for a constant exchange rate

IMS Consulting Group Data Analysis Training - January 17 2012

2 pulling data and exporting from padds3
2. Pulling data and exporting from PADDS
  • To change the layout default to absolute, select Layouts in the Report menus and then select “IMS Standard (absolute)”
  • You have to change this in each database – monthly, quarterly and medical

IMS Consulting Group Data Analysis Training - January 17 2012

2 pulling data and exporting from padds4
2. Pulling data and exporting from PADDS
  • Always export as “Flat (only lowest level)”

IMS Consulting Group Data Analysis Training - January 17 2012

2 pulling data and exporting from padds5
2. Pulling data and exporting from PADDS

The client has provided us with the following definition with which to investigate the ADHD market

  • Your European client has defined your ADHD molecule market of interest as:
    • Amphetamine & dexamfetamine
    • Methylphenidate
    • Dexamfetamine
    • Atomoxetine
  • We have extracted 12 years of quarterly Standard Units and Sales (Mfn) Local Currency*
  • The following attributes were used to define the rows:
    • Country combined
    • Mol list
    • Mol count
    • Intprd
    • Corp
    • NFC123
    • Gx Product Class,
    • Int strength
  • The client would like to compare the following countries:
    • Italy
    • UK
    • USA
    • Brazil

*”Local Currency” means at constant exchange rate

IMS Consulting Group Data Analysis Training - January 17 2012

agenda4
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel
3. Processing the data in Excel

Important tips for data processing

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel1
3. Processing the data in Excel

Sticking to certain formatting standards will allow others to understand your work faster

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel2
3. Processing the data in Excel

Sticking to certain formatting standards will allow others to understand your work faster

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel3
3. Processing the data in Excel

Sticking to certain formatting standards will allow others to understand your work faster

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel4
3. Processing the data in Excel

It is vital to maintain the integrity of your original data in case you need to quickly update it at a later stage

×

  • Insert any new columns to the right or left
  • Do not insert columns into the middle of your data

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel5
3. Processing the data in Excel

Simple relationships between the sheets improves readability and auditing

Simple relationships among sheets

Overly complex relationships among sheets

Data Sheet(s)

Analysis Sheet(s)

Data Sheet(s)

Analysis Sheet(s)

Lists & Selections

Sheet

  • This structure is bad because tracing calculations has you jumping back and forth between sheets which makes the task much more complex
  • Elements such as selections and lists are harder to find because they don’t have a ‘home’
  • It is good to have lists and selections on a separate sheet to make them easier to find and maintain good structure for the workbook

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel6
3. Processing the data in Excel

Create calculated variables and “key” variables in new clearly-identified columns next to the raw MIDAS data

Data sheet

Output sheet

  • “Key” variables are useful for “rolling-up” data into custom groups and form the building blocks for the dynamic tables
  • Create key variables in your data sheet and then use formulas such as SUMIF to group the data together
  • In this example, all the sales for a particular molecule-form have been summed

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel7
3. Processing the data in Excel

Creating custom groupings and “key” variables requires a good knowledge of different text functions

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel8
3. Processing the data in Excel

The IF function returns one value if its logical expression evaluates to TRUE and another value if it evaluates to FALSE

  • IF(logicalExpression, valueIfTrue, valueIfFalse)
    • LogicalExpression can be any valid Excel expression that evaluates to TRUE or FALSE (e.g. A1 > 0).
    • ValueIfTrue is the value that is returned if logicalExpression evaluates TRUE.
    • ValueIfFalse is the value that is returned if logicalExpression evaluates FALSE.
  • IF statements may be nested up to seven layers deep. For example, the following expression returns ‘Yes’ if A is greater than zero and less than 10 but not equal to 5.
    • IF(A1 > 0, IF(A1 < 10, IF(A1 = 5, “No”, “Yes”), “No”), “No”)

IMS Consulting Group Data Analysis Training - January 17 2012

3 processing the data in excel9
3. Processing the data in Excel

Calculating days of therapy is a common part of processing the data

  • Days of therapy (DOT), also called treatment days (Tx days), is an important and commonly used volume measure
  • It is critical to use when the therapy area has multiple forms and/or dosing regimens, since 1 standard unit is not always comparable between products
  • Days of therapy = Volume (in SUs) / average (or defined) daily dose (in SUs per day)

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 2
Exercise 2

Now that you have extracted your data, there are various processing tasks to be completed before it will be ready to analyse

Exercise 2a

  • Open file “DA Training Exercise 2a Complete Data Tables v_.xls”
    • Instructions are in the Excel file
    • A completed example is in the Excel file for Exercise 2b

Exercise 2b

  • Open file “DA Training Data Sources ADHD Sales.XLS”
  • Open file “DA Training Data Sources ADHD AVDD.XLS”
  • Open file “DA Training Exercise 2b Extend Data Tables v_.xls”
    • Instructions are in the Excel file
    • A completed example will be given to you

IMS Consulting Group Data Analysis Training - January 17 2012

agenda5
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables
4. Making Dynamic Tables

Dynamic tables improve the speed, accuracy and replicability of data analysis

  • They are much faster when analysing multiple countries, products etc.
    • With practice, a single dynamic table takes only slightly longer to create than a non-dynamic table
    • A dynamic table can be adapted very quickly to analyse different countries, products etc.
  • They reduce errors
    • Avoids errors that inevitably occur when people do repetitive tasks
    • Allows better audit and trace when errors occur or changes are required
  • They can be quickly formatted to into client-ready tools and deliverables
  • They are more fun to create

Benefits of dynamic tables

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables1
4. Making Dynamic Tables

A particularly poor method would be to use individual SUM formulae which have to be defined manually for each country and indication

×

This table is not dynamic

  • Individual sum formulae with manually defined ranges have been used
  • The means that a new formula would have to be written for each country and each indication

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables2
4. Making Dynamic Tables

Make sure that the selection criteria in your SUMIF formulae are not hard coded

×

This table is not dynamic

  • The main SUMIF formula uses a hard coded selection criteria (“GERMANY RETAILAsthma”)
  • The formula will need to be manually updated to select the data relating to a different country

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables3
4. Making Dynamic Tables

Dynamic tables should update when the source data is changed and should have the flexibility to select different categories of data

This table is dynamic, will update when new countries are added into the data, and will update when different countries are selected

  • The main SUMIF formula in the dynamic table feeds off a dynamic lookup cell
  • Data relating to new countries can be added to the analysis without the need to update the formula in the table
  • The table will also update when different countries are selected

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables4
4. Making Dynamic Tables

Useful tips for creating dynamic tables

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables5
4. Making Dynamic Tables

The SUMIF function is very commonly used in dynamic tables for shares as well as totals

Example of a SUMIF function to complete a dynamic table

  • In this example the $’s are used to fix all the ranges except:
    • the country changes with the table row
    • the sales year changes with the table column
  • To calculate shares or prices, simply divide two SUMIF functions within the same formula
  • To calculate averages, divide a SUMIF function by a COUNTIF function within the same formula

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables6
4. Making Dynamic Tables

It is a good idea to use some simple Excel names for cells and ranges to simplify formulae and make the workbook more auditable

  • Naming important columns is helpful as it allows formulae to be simplified and quicker to understand for other users

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 3
Exercise 3

In these exercises you will analyse value sales by molecule form, share of market volume and generic penetration by molecule

Exercise 3a

  • Open file “DA Training Exercise 3a Dynamic Tables v_.xlsx”
    • We will work through this exercise together before moving on to Exercises 3a-3e
    • Instructions are in the Excel file on the sheet “Exercise 3a”
    • A completed example is in the Excel file for Exercises 3a-3e

Exercises 3a-3e

  • Open file “DA Training Exercise 3b to 3e Dynamic Tables v_.xlsx”
    • Instructions are in the Excel file
    • It is likely you will not have time to complete all 4 tasks
    • A completed example will be given to you

IMS Consulting Group Data Analysis Training - January 17 2012

agenda6
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables7
4. Making Dynamic Tables

To insert forms first enable the Developer Tab then select Insert Form Controls

  • First enable the developer tab in Excel Options first
  • Then, on the Developer tab choose Insert

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables8
4. Making Dynamic Tables

Forms controls can be used to set up pick lists for intuitive user interface

Example - Objective

  • In this example we want to add a pick list to put the selected name in the blue cell

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables9
4. Making Dynamic Tables

The pick list can be set up in three simple steps

Example - Steps

  • Draw a combo box from the Forms toolbar and right-click on it and select Format Control.
  • Enter the list of names as the input range: $C$5:$C$9 and enter the purple box as the cell link: $C$4
  • Put an INDEX function in the green box to return the name selected: =INDEX($C$5:$C$9,$C$4).

1

2

3

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables10
4. Making Dynamic Tables

As a result, both the value and the number of the selection are populated in the appropriate cells

Example - Result

  • The name selection box now controls yellow cell, which controls the green cell.

IMS Consulting Group Data Analysis Training - January 17 2012

4 making dynamic tables11
4. Making Dynamic Tables

In most cases Excel Form Controls are better than using the Validation functionality

Reasons to use Excel Form Controls for pick lists:

  • Can link multiple pull-downs to the same cell
  • Is very obvious to anyone using the file that there is a pick-list vs. data validation looks like a regular cell
  • Can link to a list on another sheet without having to name the range

Reasons to use Validation for pick lists:

  • Is very efficient if doing many cells with the same list of choices (can copy and paste)

A Forms control is always visible

Validation is visible only when you click on the box

Note: to use Validation with a list on a different sheet, simply name the range for the list and use the name instead of he Excel ranges

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 4
Exercise 4

Now that the data analysis is complete, your can make it more client friendly by adding user interfaces

Exercise 4

  • Open file “DA Training Exercise 4 Forms v_.xlsx”
    • Instructions are in the Excel file
    • A completed example will be given to you

IMS Consulting Group Data Analysis Training - January 17 2012

agenda7
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis
5. Checking the analysis

There are various categories of error to be aware of

CATEGORIES OF ERRORS

Excel Errors

Logic Errors

MIDAS Errors

Inappropriate Analysis

  • Errors in the calculation logic that cause Excel Errors:
    • #VALUE
    • #DIV/0!
    • #NAME
    • #N/A
    • #REF!
    • #NUM!
  • Errors in the calculation logic which provide a number but not the one that was intended
  • Coding errors
  • Low coverage
  • Errors in the data itself
  • Does the analysis answer the right question?
  • Has it been carried out at the right level of granularity

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis1
5. Checking the analysis

Different tools and techniques help you correct errors

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis2
5. Checking the analysis

Good working practices and a focus on answering business questions will help you deliver a quality analysis

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis3
5. Checking the analysis

Formula auditing features can be used to uncover and correct most types of error

  • The formula auditing features can be found on the Formula tab
  • Trace Precedents / Dependents and Evaluate Formula are particularly useful features
  • Trace Precedents / Dependents allows you to visualize which cells are up/down-stream of a particular cell in a chain of calculations
  • Evaluate formula allows you to run through a complicated formula step by step so you can trace the source of the error

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis4
5. Checking the analysis

Correcting Excel errors is usually straight-forward

  • The Excel error message gives you a big clue about how to fix the problem.
    • #VALUE! - the wrong type of argument or operand is used
    • #DIV/0! - a formula divides by 0 (zero)
    • #NAME? - Microsoft Excel does not recognize text in a formula
    • #N/A - a value is not available to a function or formula
    • #REF! - a cell reference is not valid
    • #NUM! - a problem occurs with a number in a formula or function
    • #NULL! - an intersection is specified of two areas that do not intersect
  • The Excel Help database contains more information on how to correct these errors (see topic “Troubleshoot formulas and error values”)
  • Locating the problem is usually a matter of going “upstream” to the source of the error
    • Excel shows an error message in the cell causing the error and in every “downstream” cell.
    • Start from the result cell and work backward, looking for the first occurrence of the error message.

IMS Consulting Group Data Analysis Training - January 17 2012

functions for handling errors
Functions for handling errors

The IF function has many applications but is often used in conjunction with other functions to handle errors

  • The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE
  • Use the syntax: IF(logical_test, value_if_true, value_if_false)
  • Logical test: Required. Any value or expression that can be evaluated to TRUE or FALSE
  • value_if_true  Required. The value that you want to be returned if the logical_test argument evaluates to TRUE
  • value_if_false  Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. Returns a FALSE if no value is entered

IMS Consulting Group Excel Training - January 9 2012

functions for handling errors1
Functions for handling errors

The ISERROR function can be used in conjunction with the IF function to perform a different action if an error occurs

Value if FALSE

Value if TRUE

Logical test

Price = Sales / Volume

  • The highlighted cell returns an error because the calculation divides by zero
  • Use ISERROR in conjunction with IF to return a specific value in case an Excel error occurs
  • Note: this is can be used as an alternative to IFERROR (which does not work in Excel 2003)
  • ISERROR provides the logical test component of the IF function
  • In the formula above if C5/D5 returns an error then ISERROR returns a TRUE result
  • The IF function can be use to return a zero if the logical test is TRUE and the original calculation if the test if FALSE

IMS Consulting Group Excel Training - January 9 2012

functions for handling errors2
Functions for handling errors

The TYPE function can also be used to handle errors in the same way

Value if FALSE

Logical test

Value if TRUE

Price = Sales / Volume

  • The highlighted cell returns an error because the calculation divides by zero
  • TYPE function identifies the type of value that appears in an Excel cell
  • If TYPE is used on a cell with an error it returns the value 16 (other values are returned for other cell types)
  • The same formula as before is used except that the TYPE function replaces the ISERROR as the logical test
  • In this case the logical test will also return a TRUE because the TYPE function will return 16 due to the #DIV/0 error

IMS Consulting Group Excel Training - January 9 2012

functions for handling errors3
Functions for handling errors

IFERROR is the most efficient way to handle errors but is not available in Excel 2003

Price = Sales / Volume

  • The highlighted cell returns an error because the calculation divides by zero
  • IFERROR combines the IF and ISERROR functions to simplify the formula
  • Use the syntax:IFERROR(value,value_if_error)
  • The calculation only has to be entered once and if error will automatically revert to the value if it does not generate an error

IMS Consulting Group Excel Training - January 9 2012

functions for handling errors4
Functions for handling errors

To avoid untidy looking charts (particularly for price) you can replace the #DIV/0 error with a #N/A error using the NA function

  • #DIV/0 errors appear on an Excel chart as a 0, which is confusing on a pricing chart
  • #N/A errors do not chart at all, which makes for a more sensible looking chart

×

  • Using the NA function as the value to display in case of error allows you to convert all #DIV/0 errors to #N/A errors

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis5
5. Checking the analysis

Be sure to select and “exact match” when using lookup functions

Or

  • By typing a 0 or FALSE as the final component of the vlookup formula an “exact match” is selected

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis6
5. Checking the analysis

When a formula refers back to its own cell, either directly or indirectly, it creates a circular reference – by default these are turned off in Excel

  • This cell in the total line is now both:
    • the result of calculations in the using the cells below
    • used to calculate the cells below

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 5
Exercise 5

Before any analysis can be handed over to a client, or used to create presentation material, it must be checked for obvious Excel errors

Exercise 5a – Excel Errors

  • Open file “DA Training Exercise 5a to 5b Error Tracing v1.xlsx”
    • Instructions are in the sheet: ‘‘Ex 5a (excel error)’’
    • The answer will be discussed with the class

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis7
5. Checking the analysis

Finding logic errors calls for sharp eyes and a keen mind

  • Manually trace the calculations for a representative cell or set of cells – are there any unexpected results?
  • Create and examine a summary chart to look for interesting or surprising trends
    • Surprising trends may mean a logic error or could be a result of inappropriate analysis (e.g. a lower level of granularity may be required to uncover the true story)

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 51
Exercise 5

Errors in formula logic must also be eliminated otherwise the analysis could lead to the wrong conclusion

Exercise 5b.1 and 5b.2 - Logic Errors

  • Open file “DA Training Exercise 5a to 5b Error Tracing v1.xlsx”
    • Instructions are in the sheets: ‘‘Ex 5b.1 (logic error)’’ and ‘‘Ex 5b.2 (logic error)’’
    • The answers will be discussed with the class

IMS Consulting Group Data Analysis Training - January 17 2012

5 checking the analysis8
5. Checking the analysis

There are three main categories of MIDAS Error to be aware of

  • Coding errors – where certain products have been miscategorised (e.g. generic products labelled as original and vice versa)
  • Low coverage – where the sales for a particular product look particularly low because the data is not being picked(this could be for a variety of reasons)
  • Errors in the data itself – often caused by problems with IMS’s various data suppliers (e.g. in one month a supplier may not deliver any sales data, leading to a drop in sales for particular products)
  • Adjust coding errors with manual corrections or fomulae
  • Use the CAD sheets to check data coverage
  • If all else fails contact the MIDAS help desk
  • Sometimes we must accept that certain analyses are simply not possible

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 52
Exercise 5

Sometimes the errors will be the result of MIDAS data, these must also be removed

Exercise 5c – MIDAS Errors

  • Open file “DA Training Exercise 4 Forms v_.xlsx”
    • Find the product that is classified as neither an original nor a generic product (in sheet ‘‘ADHD Sales Data’’)
      • Why might this product have a non-specific classification in MIDAS?
      • If we were to re-classify this product, would you recommend to re-classify it as a generic or as an original product, and why?
      • How could we implement the re-classification?

IMS Consulting Group Data Analysis Training - January 17 2012

exercise 53
Exercise 5

Finally, before the analysis is handed over, you must be sure that it answers the business question at hand

Exercise 5d - Inapproproate analysis

  • Examine the chart below from the answer to Exercise 4.
    • What is the question being asked?
    • What does the chart mean? How would you interpret it?
    • What could be causing the deviations in the line (red arrows below)?
    • How could we change this analysis to give better insight into the originals’ share?

IMS Consulting Group Data Analysis Training - January 17 2012

agenda8
Agenda

IMS Consulting Group Data Analysis Training - January 17 2012

what have you learned
What have you learned?

We hope you have learned practical skills and good practices for sales data analysis

  • 5 steps to consider when undertaking MIDAS data analysis
  • Things to look out for to understand sales data for therapy areas, drug classes and countries
  • A recommended approach to pulling and processing MIDAS data
  • New techniques:
    • Use of functions to create dynamic tables
    • How to replicate tables for multiple countries / products
    • Some simple user interface tools (forms)
  • Standards for colours and formats that will improve consistency and readability across tools and projects
  • Good practices to work cleanly, quickly and accurately

IMS Consulting Group Data Analysis Training - January 17 2012

data analysis steps1
Data analysis steps

Remember to consider all 5 steps in your work plan for MIDAS data analysis

5 Data Analysis Steps

1. Understand your therapy areas and countries

2. Extracting data from PADDS

3. Processing the data in Excel

4. Making dynamic tables

5. Checking the analysis

IMS Consulting Group Data Analysis Training - January 17 2012

action planning
Action Planning

Please take a few minutes to fill out the evaluation forms

IMS Consulting Group Data Analysis Training - January 17 2012