11 Replies Latest reply on Jun 5, 2012 3:15 AM by Sorbsbuster

    Line chart as a timeline

    Mitch

      Title

      Line chart as a timeline

      Post

      I was reading a basic description of how a line chart can be formatted as a timeline.  

      It requires the use of a 'dummy table', with one record created for a date within the date range. In this table, there is a calculated field showing the date fields that have records for specific dates, which then become the timeline's milestones, appearing on the relevant day.  It is the calculated filed that becomes the X axis. 

      Does anyone know how the 'dummy table' is created, and how records are created within this table for each day within the date range?

        • 1. Re: Line chart as a timeline
          Sorbsbuster

          I would suggest you create 30 records in the table (to make a month of days).  Set a script to show all records, and Replace Field Contents or loop through the records setting each to be the next sequential day, after a user-specified start date, say.

          So for a month you have one record per day.

          Create a relationship from that table to the data source table, linking date to date. Presumably you have a value in the source table that is the total or whatever by day that you want to plot.

          In the 'dummy' table create a field 'YValue', = the value in the source table you are trying to plot, using that relationship.

          You can now draw a chart with the date on the x-axis and that value on the y.

          • 2. Re: Line chart as a timeline
            Mitch

            The table I wish to use as the main source of data, is on based on a persons carrer and entries are recorded in years.  I do of course have a start year (I can get using function Min) and end year (using function Max).  The main source table is also a portal.   Also, some years may have more than one entry.   There may only be a dozen or so entries in a career span X number of years.  eg:  Records for only, 1887, 1889, 1890, 1890, 1893, 1893,1894.

            Is there any way I can generate records within this table representing the 'missing years' in the career portal, such as 1888, 1891 and 1892?  But how does this go when a user adds a new entry for 1895, or second entry for 1890?.  Or, Am I on the wrong track all together?

            On a differant approach, I have had some success by having a second table, that I can use a script to generate records for every year, starting at the earliest year, 1887 and ending in the lastest year, 1894.  A total of 8 records.  I can plot this data a line graph, but I dont know how to get the relevant records from the career portal over to the relevant year entires in the new table, displaying only these records, giving me a true timeline graph.  But, even if I could do this,  how would it work if there are two entries for the one year?  I assume just another record.

             

             

            • 3. Re: Line chart as a timeline
              Sorbsbuster

              I'm unclear about what you want.  I assumed the values on the x axis were regular time periods, and the values on the y axis were conventional numeric values.  What values do you mean to show on the y axis?  How would you want the value displayed for two 1890 entries?

              • 4. Re: Line chart as a timeline
                Mitch

                For the timeline (which is just a line graph) there would be the value '0' for all Y axis records and the Y axis reduced in height to only show a single horizontal line which would represent the X axis.  The X axis values are taken from my timeline portal recording a persons career. My timeline portal has the fields ID, YEAR, RANK, DETAILS and also a combined text field based on the data from YEAR, RANK and DETAILS.  The portal records are sorted by year and it is the combined text field I wish to dsplay on the X axis.  I can produce a basic line graph as detailed above now, dispalying my combined text field.

                However the lowest year would be at the left of the line graph, the highest year at the right of the line graph and each record evenly spaced in between. This is a true line graph. A timeline however would have the spacings between records (sorted by years) relative to the difference between the actual years. 

                Therefore, I wish to create the appearance of a timeline.  To generate a true timeline, I would assume I would need an 'additional' record added for each year between the lowest to highest year recod and only the records that have combined text field data displayed (which would create the spacing required). I also assume it can not be the actaul timeline portal that has this 'additonal' record added and the table used for the graph would need to be generated at the time...?    As for two records with the same year, they would just be another record.   They can have a secondary sort by DETAILS.  

                Hope this all makes sense....?

                 

                • 5. Re: Line chart as a timeline
                  Sorbsbuster

                  I think it would be easier to do it vertically.

                  I assume you only have records for each point of change, and you mean something like

                  Tommy Atkins

                  1887 Private
                  1888
                  1889 Lance Corporal   Corporal 
                  1890
                  1891 Sergeant
                  1892
                  1893 2nd Lieutenant   1st Lieutenant
                  1894
                  1895
                  1896 Captain             Major                General
                  1897
                  1898

                  • 6. Re: Line chart as a timeline
                    Mitch

                    Yes.  Exactly.  The records with no rank and details woul ony appear as spaces. In the exampe you have given there would be a greate space between 1893 and 1896 to represent the space in years.  

                     

                    1887 Private
                        
                    1889 Lance Corporal   Corporal 
                       
                    1891 Sergeant

                    1893 2nd Lieutenant   1st Lieutenant

                     

                    1896 Captain             Major                General

                    • 7. Re: Line chart as a timeline
                      Sorbsbuster

                      Create another table, with two fields, Year and Ranks.

                      Create as many blank records as years you want to see in the range.

                      Link the table to your source table by year.

                      Go to the first record and Replace With Serial Numbers, starting at teh first year you want and incrementing by 1.

                      Create the Ranks field as a calculation, Substitute ( List ( CareerData::Rank ) ; "¶" ; ", " )

                      Conditionally format the Year to be white text if RankList is blank.

                      A lot of that can be scripted.  You can also make it appear horizontally but it's more work.

                      This is what you would see in a list view of the new table.

                       

                      • 8. Re: Line chart as a timeline
                        Sorbsbuster

                        The png screenshot refuses to upload.  Just trust me that it works.

                        • 9. Re: Line chart as a timeline
                          Mitch

                          Ok.  Thanks.  Will try it and get back to you.

                          • 10. Re: Line chart as a timeline
                            Mitch

                            Go to the first record and Replace With Serial Numbers, starting at teh first year you want and incrementing by 1.

                            I have created the new table and a script that generates the number of record in the new table based on the differnce between the YEAR fields Min and Max value.  

                            Is there anyway I can set the start serial number for the first record in teh new table to match the YEAR feilds Min value, as this will change with each record, as does the number of record I need to create....  

                            • 11. Re: Line chart as a timeline
                              Sorbsbuster

                              You could emulate the Replace function with a loop script.  Having created the correct number of records:

                               

                              Enter Browse Mode

                              Set Variable ($Year ; StartYear (wherever you got that value from) )

                              Go to Record [First]

                              Loop

                              Set Field ( Year ; $Year )

                              Set Variable ($Year ; $Year + 1 )

                              Go to Record [Next; Exit after last ]

                              End loop

                               

                              You could combine the script that creates the records with these steps - all you have to do is Exit the script when the $Year reaches the end year.