9 Replies Latest reply on Jan 13, 2012 3:38 PM by philmodjunk

    Analytics Sort by Metro Name Summary then by Rank Listing

    MacMak

      Title

      Analytics Sort by Metro Name Summary then by Rank Listing

      Post

      Hello.

          I have records in one table that contain metro names, id's and ranking from another table called 'Metros'. They are related by a metros foreign key.

      I have an analytics layout that displays Metro Names from the 'Metros" Table Vertically and a MainTable::metro_summary which contains a Summary of metro id's from a foreign key field in the main table.

      I need to be able to sort each metro but also sorted by a rank number that each metro id is associated with so the top will be metro number 1 then metro number 2 etc. The chart type is a horizontal bar.

      Thanks again and any help would be appreciated.

       

        • 1. Re: Analytics Sort by Metro Name Summary then by Rank Listing
          philmodjunk

          Are Metros and MainTable the only two tables here?

          is the relationship similar to this?

          Metros::MetroID = MainTable::MetroID

          Does your analytics layout list "metros" or "MainTable" in "show records from" in layout setup?

          "sort each metro but also by a rank number" isn't sufficiently clear here.

          Do you have multiple records in MainTable with the same "metro" (metroID?) value?

          Is the rank a number field in MainTable?

          If so, you should be able to select sort records... from the Records menu and specify a sort first by "metro", then by rank.

          The chart type is a horizontal bar.

          Didn't know a chart was involved until we reached that sentence. Is this a chart with the "use data from current found set" and "show data points for groups of records when sorted" options selected?

          • 2. Re: Analytics Sort by Metro Name Summary then by Rank Listing
            MacMak

            Hello. Please see me answers below:

             

            Are Metros and MainTable the only two tables here? In this context yes but there are more tables not used here

            is the relationship similar to this? Yes

            Metros::MetroID = MainTable::MetroID

            Does your analytics layout list "metros" or "MainTable" in "show records from" in layout setup? Right now it shows Metros sorted by name from metro::metro_name and horizontally maintable::metro_summary. I was trying to do a script fro a button on that layout that would sort by the metro_rank but maybe I need to do another layout for that?

            "sort each metro but also by a rank number" isn't sufficiently clear here. Meaning I could get the analytics displayed both as metro by name descending and metro by rank descending.

            Do you have multiple records in MainTable with the same "metro" (metroID?) value? Yes. I many records (contacts) that use the metros from the metros table.

            Is the rank a number field in MainTable? No. The rank is a field in the metros table.

            If so, you should be able to select sort records... from the Records menu and specify a sort first by "metro", then by rank.

            The chart type is a horizontal bar.

            Didn't know a chart was involved until we reached that sentence. Is this a chart with the "use data from current found set" and "show data points for groups of records when sorted" options selected? Yes to both.

             

             

            Thanks for your help!

            • 3. Re: Analytics Sort by Metro Name Summary then by Rank Listing
              philmodjunk

              There's some confusion here over "horizontally" and "vertically" sorting data. You can only sort the data in your found set, into ascending, descending or a custom, value list specified order. This sort order will then affect what you see in a chart of the same data.

              You can sort on one field first, such as metros, then sort on a secondary field (rank) to order the metros records in each group by rank. But I don't think that's what you want here.

              Please describe the chart you want, what each bar is supposed to represent and the order you want to see for the bars listed in this chart.

              I just can't quite picture how "metros" and "rank" relate to each other on your chart.

              • 4. Re: Analytics Sort by Metro Name Summary then by Rank Listing
                MacMak

                Here's the scenario:

                Sort 1 - Metros by name

                Metro Names from top to bottom (A -Z)

                Number of contacts in each metro (length of bar) left to right.

                This I can do but not the next.

                Sort 2 - Metro Names by Arbitron Rank (Each Metro has a rank number such as Los Angeles = 1, New York = 2 etc.)

                Metro names by Rank top to bottom

                Number of contacts in each metro (length of bar) left to right.

                 

                I believe the problem is that the rank field is a lookup field from the relationship between main table and metros table. So, I can cannot do a summary in main table that reflects this field and use it in my analytics.

                I hope this makes sense as I would love to be able to do this.

                 

                Thanks!

                • 5. Re: Analytics Sort by Metro Name Summary then by Rank Listing
                  philmodjunk

                  Are the rank values unique? (Can two metros have identical rank values?)

                  I do not see why you cannot simply sort by rank, then by metro since both fields are in the metro table. This should give you individual metro bars, but sorted by rank. You can even sort by values in related tables--you are not limited to sorting on fields in the layout's table--though this does not appear to be the case here.

                  • 6. Re: Analytics Sort by Metro Name Summary then by Rank Listing
                    MacMak

                    Yes, the rank values are unique. 1 unique rank (id) for each metro.

                    I have my sorts from analytics done from a summary field as in metro_summary (number of contacts in Los Angeleles Metro, New York Metro etc.)

                    I think the problem is that I have other tables conected to Metros as well and  I just want to see 2 things:

                    1:

                    How many contacts in each metro sorted from A-Z in Horizontal Bar

                    2:

                    How many people in each metro sorted by the metro ranks.

                     

                    Number 1 I just do a summary field from the main table using the metros foreign key

                    Number 2 I don't have a foreign key for since it is related to the metro foreign key and I use the rank field as a lookup from that connection.

                    • 7. Re: Analytics Sort by Metro Name Summary then by Rank Listing
                      philmodjunk

                      Keys are not the issue here as far as I can tell.

                      How are the ranks determined? Is this a calculation field? A Summary field? How is it defined? If "rank" is a simple number field in metro, there should be no problem with sorting on that field. Sorting on a Summary field, however, will be a problem.

                      • 8. Re: Analytics Sort by Metro Name Summary then by Rank Listing
                        MacMak

                        The ranks are just numbers given to each metro it is not a calculation. Basically in my metro table I have these fields

                        Metro Primary Key

                        Metro Name

                        Metro Rank by Arbitron (a unique number given to each Metro Name - not a calculation)

                        Metro Rank by Nielsen (Another similar ranking system)

                         

                        I just need analytics to show

                        Los Angeles (first as its rank number is 1) and then the vertical line indicating how many contacts in Los Angeles.

                        New York (second as its rank number is 2) and then the vertical line indicating how many contacts in New York.

                         

                        and go through like this for all the metros.

                        • 9. Re: Analytics Sort by Metro Name Summary then by Rank Listing
                          philmodjunk

                          Then you can sort by either metro rank.

                          How do you compute total contacts?

                          I had assumed you had a summary field defined in metro that counted metro records or totaled a number field. If that were the case, you would jsut sort by a rank field and your chart would show the totals from the summary field. The same summary field would work for both sorting by Metro and sorting by rank.