2 Replies Latest reply: Feb 18, 2013 8:41 AM by phospholipid77
phospholipid77 Level 1 Level 1 (0 points)

Hey there. I'm working on a migraine journal for folks in my family. I looked for a premade one and none of them hit all of the data we need and could organize it how we needed it organized.

 

What I've made so far has, like, a million data points. The graphs are all split by the day which goes along the top. Under each day there is an entry for each kind of food - whether or not it was ingested that day - as well as markers for pre-migrain events, sleep, stress, exercise, drugs, herbals, supplements, migraine events, pain, quality, aura, other features, etc. I got it to sync between desktops and phones/pads and that is fantastic. Largely, this is all as check boxes. If somebody eats aged cheese that day, a box is checked. If they took a nap that day, a box is checked. Some things have steppers. Stress levels are entered as a 1-5, as is sleep quality.

 

Now, however, I'm having a really hard time figuring out a good way to visualize relevant data.

 

I currently have five charts spread across three sheets: Diet, Wellness, Induction, Events, Medication. I'd like to make a fourth page that visualizes the data. So, for example, a long graph with dates along the bottom that shows 1) when a migrain occurred and 2) all relevant events (from Diet, Induction, and Wellness) that happened in the week leading in. I want to leave out some zero data. If the user didn't eat chicken, there's no need for a representation on the graph. However some zero data I do want; if they didn't wake naturally that day, it might be an important variable.

 

I have no idea the best kind of chart to use, or how to program it. I ended up with one super long graph (400 inches) that followed the timeline over the next few months and was actually visible. But, it wasn't really useful. I'd love any and all thoughts. It's a tall order, I think, and this is not my forte. I'm not a big data analyst.

 

JH

  • Barry Level 7 Level 7 (29,215 points)

    Hi JH,

     

    Numbers can chart only numerical values, so the first thing you will need to do is to determine a means of assigning a numerical value to each checkbox.

     

    Once you have that done, I'd suggest using a stacked bar graph to display the triggers, and overlay a bar or line graph to represent the migraine events.

     

    Here's an example. Checkboxes in rows 2 through 8 are given a value of 1 if checked, zero if unchecked. In the last two rows, those values are reversed; checked = 0, unchecked = 1, to represent values for 'didn't wake naturally' and similar cases.

    Picture 6.png

     

    The formula in B2 of table 2 is: =IF(Table 1 :: B2,1,0)

    This is filled right to column D, and down to row 8.

     

    The formula in B9 of table 2 is: =IF(Table 1 :: B2,0,1)

    This is filled right to column D, and down to row 10

     

    Changes made from the default chart values assigned by Numbers:

     

    The small square with three white lines (above "Factor 1" controls the direction in which Numbers sees a series of data running. Default value is vertical. Click the square to change that to 'horizontal' as shown.

     

    Colours for each series have been changed to permit each series to be represented by a different colour.

    Click the small coloured square at the end of each row of data, then click the Fill Color well in the format bar and choose a colour for each factor.

     

    Stepping of values has been reset on the Y axis. For your case, I would also set the minimum and maximum values on this axis to fixed values, then adjust the number of steps to make the values in the labels all even numbers.

     

    This should give you a start. For more on creating and formatting charts, see the Numbers '09 User Guide. You can download the guide via the Help menu in Numbers.

     

    Regards,

    Barry

  • phospholipid77 Level 1 Level 1 (0 points)

    That is *exactly* the kind of direction I needed. For whatever reason, I wasn't having that good of a time with the online documentation I found. This is a fantastic start. Thank you so much.

     

    Cheers.