Axis Tick Mark Labels from a formula

[david brownParticipant]

I would like to make graphs where the x-tick-mark labels are text and come from one of two columns, depending on the value in a third column. In other words, the result of an if(a,b,c) function if Data Graph allowed for text to be used in an if statement. I have attached a marked up screenshot of my Data Table. The situation is made more difficult because my x-axis data are dates, for which a second axis is not available.

In this case, I want some of the x axis tick mark Labels to be just the month, with the others being the month and year.

The columns in the Data Table are:

The Location of each tick mark is in column “TickLocation”, which is a date. It is an expression column, the value of which is computed using the desired spacing between tick months; in this example, 3 months.

I would like to derive the x axis tick mark Labels from the other columns, as follows.

Column C is the month of the tick mark. Notice that the month is a text item. I would like this to be the tick mark Label for some of the tick marks.

B is the year of the tick mark.

Column E, a text expression, is “Month Name”\n”TickYear” (i.e. in terms of columns, C\nB.) This is the Month with the Year underneath. I would like this to be the tick mark Label for some of the tick marks.

Column D is a flag as to whether the Label for that tick mark should or should not include the year (it is an expression). If the value is zero, the Label should be Column E; if not zero the Label should be Column C.

Were it not for the fact that the Month is text, this would be simple. I have scoured the Knowledge Base and Forum, and examined many of the Example files. I have seen many clever ideas, but could not put them to use. I tried to create an appropriate Mapping, but was unable. An essentially identical question was asked in the forum, with the following answer, but I did not understand it.

You would have to use a numeric variable or have the text variable used in other ways.  For example, if you were plotting either expressions and only showed the plot based on a mask that was tied to a global variable.

I would appreciate any ideas or solutions. Based on what I have seen thus far, I anticipate that one of the Data Graph team genius’s will come up with something.

Lastly, because in this case the x axis data is a linear series of dates (i.e. one date point per day, without gaps), one approach is to make the x axis # rather than the date. Doing so allows use of a second axis, which can solve the problem. This has the drawback that hovering over a data point does not show the date, but the row number. That is not a huge limitation.

The question is really about using text rather than numbers or dates, which eliminates use of many functions, etc.

• This topic was modified 2 years, 6 months ago by david brown. Reason: Added last two paragraphs

[dgteamModerator]

Sounds like you are looking to modify the default format, which only show the year for the month of Jan, to show the year for the first and third quarters. Something like this …

To do this, I started by creating a column to specify my tick mark locations, similar to what is in your file. Then an if statement is used to check whether it was the first or third quarters:

if(month(TickMarks)== 1 || month(TickMarks) == 7 , year(TickMarks),nan)

This returns either the year or nan.  As you noted, the if statements do not allow text.

Once you have the column with the nan, a mask column is used to hide the nans.

I initially tried combining the Year column (one with no nans) to get the label, but the Text expression will show blanks for the empty columns.  We have to think about if this should have worked for a future version.

What did work was to create two Text expression columns.

The first text expression column effectively converts the Year column to a column that has a Text data type and combines the \n (called YearText).

The second text expression column combines TickMarks as a token set to show the month, with the YearText column.

This approach allows the axis to plot the dates directly and the Axis settings are used to specify the tick mark locations and the label column.

Here is a closer look at the column definitions.

Sorry to take a few days to get back to you on this one, but hopefully this helps!

 

[david brownParticipant]

Thank you for a clever solution. I am adept at working with data in spreadsheets, but new to DataGraph. I have learned many techniques for working with data in DataGraph from your thorough and useful responses to my questions, as well as those of others. I very much appreciate them.

[Author]

Posts