Sorting vertices via date?

Apr 6, 2012 at 3:56 PM

I'm sure there is a very simple way of doing what I want to do, but I seem to be having a blonde moment ...

I have an Excel spreadsheet of a few thousand telephone calls: Originating number, receiving number, date and time of call, cell tower used, etc. What I want is to have a directed graph of all the calls between the various telephone numbers, I want to use a filter so that I can choose two dates and then have the graph display only those calls between those dates, the more the calls, the thicker the edge.

I have input the originating number as Vertex 1 and the receiving number as Vertex 2 in the Edges tab. I then renamed Column N (Other columns) to Date and pasted the date/time for the call refered to in Vertex 1 and 2. I have also input the subscriber's name and telephone number in the Edges sheet. The graph is Directed.

What is the best way to choose between two dates? Dynamic Filter gives some weird numbers to choose from and using custom filters works ("All dates in the period", but then I have difficulty including the subscriber's names as that edge does not have a date.


1. How do I create an Edge label that shows the number of edges (without merging the edges)?

2. Is it possible to have the Label column in the Vertices tab automatically populate with the vertex name in column A?

Many thanks!

Apr 6, 2012 at 5:22 PM

Simple one first:

To populate the Label column on the Vertices worksheet with the vertex name from column A, do this:

1. In the ribbon, go to NodeXL, Visual Properties, Autofill Columns.

2. In the Autofill Columns dialog box, select the Vertices tab.

3. On the Vertices tab, set the "source column" for Vertex Label to "Vertex".

4. Click the Autofill button.

-- Tony

Apr 6, 2012 at 5:28 PM

On the filtering issue:

When you tried NodeXL's Dynamic Filters, you said that you were given some weird numbers to choose from.  Try formatting your entire Date column as Date (Home, Number) before opening Dynamic Filters.  That should give you date pickers to choose from in the Dynamic Filters dialog box.

-- Tony

Apr 6, 2012 at 6:05 PM
Edited Apr 6, 2012 at 6:18 PM

And finally, to create an Edge label that shows the number of edges between the edge's adjacent vertices:

1. On the Edges worksheet, format the Label column as General.  (It is formatted as Text by default, which won't accept formulas.)

2. In the first empty cell of the Label column, enter this formula:

=COUNTIFS([Vertex 1], A3, [Vertex 2], B3)

That says "count the number of edges for which Vertex 1 is the value in this row's Vertex 1 cell AND Vertex 2 is the value in this row's Vertex 2 cell.

-- Tony


Apr 7, 2012 at 9:27 AM

Many thanks for the prompt feedback Tony, greatly appreciated and although I had custom formated the date column as dd/mm/yyyy hh:mm", will experiment again with the Dynamic Filter.

Apr 7, 2012 at 3:12 PM

Populating the Label column and the Edge count formula works well, thanks!

However, I'm still not filtering correctly with dates. To keep things simple, Column N ("Date") in the Edge tab is just the date without a time, formated as "Short Date".

Clicking on Dynamic Filtering brings up the box with the following options:
- Edge Filters: Label
- Vertex Filters: X
- Vertex Filters: Y

There is no option to filter on Date.

Using a custom filter in the Date column works well, but then I cannot show the Subscriber name (Vertex 1) to Tel number (Vertex 2) edge as it does not contain a date and is thus excluded from the filter?

Apr 7, 2012 at 8:13 PM

When NodeXL's Dynamic Filters feature determines which columns to filter, it skips columns that have an empty first cell.  And if the first cell is not empty but other cells in the column are empty, the edges with the empty cells get hidden by Dynamic Filters.

So a possible workaround in your case is to fill the empty cells with some dummy values, perhaps a date that precedes or follows all the real dates.

We are considering adding more control over how empty cells are handled by Dynamic Filters.

-- Tony

Apr 8, 2012 at 9:11 AM

Thanks, gives me a better understanding of why the Dynamic filter skipped the Date column.

If you are going to change how empty cells are handled by Dynamic Filters, perhaps put in a tick box to include/exclude empty cells?

Apr 9, 2012 at 3:58 PM

Yes, exactly that.

-- Tony