Creating Data Analysis Report for Stakeholder

Creating Data Analysis Report for Stakeholder

This article will review the process for preparing a data analysis report for a corporate stakeholder (employer/physician group). To complete this task, you must have Administrative rights in the UMEDTech/SKY Platform. If you have been assigned this task and do not have these rights, please contact your direct supervisor. 

STEP 1: Login to the UMEDTech platform 

STEP 2: In the black admin bar at the top of the site, go to > Content 


STEP 3: Select "Biometrics and Functional Capacity" (NOTE: it may no appear at the top of the list depending on how many other instruments have been added or updated). 


STEP 4: Click on Results > Download 



STEP 5: Select "Delimited text" > Ensure Delimited text format is set to Comma (,) > Click "Download"


STEP 6: Save the document and then upload to Zoho Docs (or you may use it in Microsoft Excel if you prefer). (NOTE: This document contains PHI and it is imperative that you save it in a secure place that is password protected from any type of breach). 



STEP 7: Delete Rows 1 and 2 and Delete columns A through I



STEP 8: Highlight all columns up to AO and select Filter 


STEP 9: Remove all names of those that you don't want to be included in the analysis. Do this by highlighting the rows you want to remove > Right Click > Delete Row (At this time we are unable to export data based upon group. This feature will be coming in future updates). 



Once you have completed removing all names that you don't want included in the analysis, you will go through each name and ensure that all data entry is complete for each member. To organize this, please follow these steps. 

STEP 10: Create a new sheet and rename both tabs "Complete" and "Incomplete" members



STEP 11: For any member who's data is not fully complete based upon the timeframe you are looking to analyze, you will cut that row and paste it onto the "Incomplete Member" tab.  For example, if you are doing 3 month analysis and there are some members for whom there is only Initial Evaluation Data entered, you will move them to incomplete tab. 

To move a member, simply highlight that row > Right Click > Cut then navigate to the Incomplete Member tab and Right Click > Paste 

For those that you consider "complete" but you find they are missing random data points throughout their record, please follow these steps: 

  • Highlight the cell that is missing the data point YELLOW
  • Create a Task in Zoho Connect and title it Missing Data for [Insert name of Stakeholder or Member Group]
  • Each task on the list will be what exactly is missing - example Jane Doe missing 3 month VO2 
* A missing data point doesn't mean you move the whole member to the "Incomplete" tab; it just means we either have to get that data or we have to account for it when reporting the "N" for that specific parameter 

STEP 12: Insert a Change Value Column after each parameter that you want to analyze. Typically these parameters would be weight (lbs), systolic BP (mmHg), diastolic BP (mmHg), BMI, Percent Fat (%), Waist Circumference (inches), hip circumference (inches), and Change in METs (ml/kg/min). If you are doing 6 month analysis, you will create a column for 3 month change and 6 month change. If you are just doing 3 month change, you will only insert a 3 month change value. To do this, Right Click and click Insert Column Before/After.  

Ensure your Change column indicates how each value is measured which is represented in the initial column



STEP 13: After setting up all of your change value columns, you will now go through and calculate the members actual change value. To do this, you can create the equation in one cell and then copy and paste it down the column. 

The equation should be written as: 3 month value - Baseline Value such that the change value is reflected as either a positive or a negative. For example, if the person's baseline weight was 200 and their 3 month weight was 175 the equation you put into the cell for 3 month change value is 175 - 200 (of course you are selecting the cell in excel that reflects that value so it would likely read: = D4 - D3 and the change value should be - 25. 



You can copy this equation, and click Ctrl V to paste the equation into relevant cells. (NOTE: you CANNOT copy and paste it down the whole column. You must just place it in one cell for each member such that it only calculates the change for that member and not include a cell from another member.) 

Once you have completed calculation of change values for all parameters, save the document (if you are not using Zoho Docs) and you may move to the 2nd part of creating the report which is outlined in the next article. 

STEP 14: Beta Blockers 
  1. Refer to column "Is this member on Beta Blockers?"
  2. If yes, please remove the VO2 value in the cell
  3. Refer to the column "NOTE: Beta Blocker".  You can remove this entire column.

STEP 15: Calculate METs
  1. Determine which VO2 test was performed:
    1. 8 Minute Treadmill Test- use the first column reading "Estimated VO2 Max (ml/kg/min)"
    2. 6 Minute  Walk Test- use the second column reading "Estimated VO2 Max (ml/kg/min)"
  2. Once test is determined, add a column to the right of the identified "Estimated VO2 Max (ml/kg/min)"
  3.   
    Label that column "METs"
  4. To convert VO2 to METs, use the number listed in the cell for Estimated VO2 Max (ml/kg/min) and divide by 3.5. 
  5. Continue for all Estimated VO2s
  6. Proceed with adding a Change in METs column and complete the steps outlined in STEP 11 
STEP 16: Create Participant ID Number
  1. Add a column to the right of each participant's name, "Participant ID"
  2. Make up a number for each participant and enter it into the cell next to their name
  3. Save the file and then copy and paste onto a new sheet
  4. On the new sheet, remove the participant's name, leaving only their ID number as an indicator of who the information belongs to
STEP 17: Add additional values as necessary prior to uploading to Zoho Reports
On your spreadsheet you Might also want to show:
  • Average polar minutes/week
  • Display number of LEC attended
  • Total Points of Contact
  • Indicate by Nutrition Intervention (Anne Moreau vs. Registered Dietician, etc.) 
STEP 18: Import spreadsheet to Zoho Reports



STEP 19: Add New Charts/Graphs
  1. Using the " + " sign in the top right of the Zoho Reports Screen, click on "New Chart"
  2. Drag and drop your values for each chart you wish to demonstrate: 
    1. The Participant ID is your X Axis
    2. your Y Axis will be the first measurement for Change that you wish to demonstrate such as Change in Weight
  3. Make sure the drop down label of the axis you drag over is selected as "Actual" and not "Sum" or any other
    1. When you select Actual and then Generate your graph, you will not have as many graph options which is ok.
  4. Save each X and Y Axis, title and then continue to the next measurement you wish to show as a graph/chart
STEP 20: Start a New Dashboard
  1. Click on the " + " sign in the top right corner
  2. Drag your first graph onto the screen
  3. Move around your graphs as you see fit
  4. Add widget and select the column that you want information from.  The goal is to show Minimum Change, Maximum Change, and Average Change that will pertain to each graph.  You can change the label from the default of the Min/Max/Ave amount by going into "Settings" 


NOTE: If you need to change any value/title in the sheet once it has already been uploaded to Reports, you will need to select "Edit Design" in the top right of Zoho Reports









    • Related Articles

    • Clearing your Computer's Browsing Data

      In computing, the web browsing history refers to the list of web pages a user has visited recently—and associated data such as page title and time of visit—which is recorded by web browser software as standard for a certain period of time. If you are ...

    • Creating a MINDBODY Account

      If you have recently joined a program with UMED and have been instructed to create a MINDBODY account, use this article as a guide to walk you through this process. 1. Access the MINDBODY website using the link provided by your Program Manager. 2. On ...

    • November 2018 UMedTech/SKY Release Notes

        Hello Everyone,             In an effort to continue to keep you informed of recent updates within our UMedTech platform, we want to share the most recent release notes with you. This month the team has been working hard and have made some great ...

    • Release note v.6

      Hello UMED members and users, Spring is upon us and as winters thaw releases it grip, we hope you're as excited as we are for 2018. This year provides exciting next steps in our platform and services which we look forward to the showcasing them to ...

    • UMedTech (SKY) Role/Permissions Assignment

      This article will explain the purpose for specific Roles and Permissions assigned to each individual user. Each user accessing the UMedTech portal will be assigned a specific role with relating permissions based upon their relationship with each ...