Grouping x axis labels by month and year

[adam.short]

Hi,

I'm having some difficulty with labelling the x axis of a bubble chart. The look I'm going for is to have month names at the ticks, regardless of whether there's data, followed by a row showing the year, with the bracketty things showing which months are attached to which year where multiple years are shown.

We have done this before in other dashboards and it has worked perfectly, but for the life of me I just can't seem to duplicate it in the dashboard I'm working on. I have copied the properties from a working example (from the x axis properties), but I still get the wrong output. What I'm seeing is two rows, as expected, but both rows are formatted like "January 2017". I know there's the option to change the format in the properties and specify a format string like yyyy or MMMM or whatever, but we haven't done that in the one that works, and if I do that I don't get the nice brackets to show the grouping. Is there something I'm missing somewhere? Maybe in how I've set up the hierarchy or something? I will try to add some explanatory images so you can see what I mean shortly.

Thanks

Adam

[adam.short]

Ok, sorry for the delay, but I managed to get some screenshots that I hope illustrate my confusion. The first four images are of a working axis configuration. As you can see, the top image shows the resultant chart, which has shortened month names in row 1, followed by a grouping of years in row 2. Ignore the strange character in the Interval field, it's just Lastpass being overzealous and thinking that field might be something I'd want to paste a password into... The content of that field is 1 in all cases.

This set are from the chart I'm currently working on. As you can see, the x axis label is a mess. The intention is that it should be exactly as the one above, and as far as I can tell, I have configured it in exactly the same way. I'm hoping someone can take a look at the screenshots of the configuration and tell me what mistake I've made, because I can't see it. The data for the x axis is in both cases coming from a hierarchy made by replacing a datetime in the data with a time dimension (Year->Quarter->Month->Day if that matters).

Thanks for any help.

[james.davis]

Do the Metric Sets have the Same Time Hierarchy?

[Inactive-Member-39400045]

Hi Adam,

Generally, the process to achieve your desired results looks as follows:

1. Drag the Date column into the data analysis panel.

2. Ensure a time dimension is applied to the date column by dragging on top of the column from the explore panel:

3. Click the “Visualization” button on the data analysis panel, expand Axis, then delete all columns in the “Bottom Axis Label” section.

4. Drag “Month” from the time dimension into this section.

5. Drag “Year” into this section. The results should look something like the following screenshot:

The bottom axis should now be arranged similar to the following screenshot:

The likely reason you cannot reproduce this behavior is due to having only a single column powering the “Bottom Axis Label” section of the visualization tab:

This would limit the amount of time dimension configurations you can visualize in the label to a single configuration, hence why you see a duplicate label in each row. Therefore, you can just drag each level into the bottom axis label section and the rows would auto-generate in the properties panel for each:

Let me know how it goes!

[adam.short]

Thank you, that helped enormously. I've now got things looking almost correct. The one thing that's still not behaving quite as I'd expect is the month labels. They look correct, but they only show up where there's data. I'd like to be able to see the labels where there is no data too. I already have Interval Method set to "Show Regular Intervals", which is the setting I assumed controlled this behaviour, but I still only get labels where there's data, so the axis labels jump from Sep to Nov if there are no data points in October. Any idea what I need to do to resolve that?

Thanks for your help. I would have continued to flail around for solutions and swear for quite some time before I stumbled on that.

[james.davis]

Try changing in the metric set on the measure how to handle null values. Try change it to fill with zero but that makes it seam like there was an entry so not sure if you would want that.

[adam.short]

I've given that a try, but it doesn't affect the output, I'm afraid. In fact, it doesn't seem to matter what I change in the properties of the measure, nothing happens in the chart. I can set it to ignore nulls, source nulls, full nulls, and then all the different values (zero, empty, etc) and nothing changes.

[Inactive-Member-39400045]

Setting the interval method to "Show Regular Intervals" will actually skip over some of your data points at a set interval. The option we need to choose here is "Show Data Point Values":

This should be done within the properties of the bottom axis, and on the "Axis" tab. This will ensure that each data point is visualized and we are not skipping over any values.

Changing the missing data rule for the measure would show extra data points outside of your dataset. For example, visualizing a zero value for dates which are outside of your dataset range. These would still be hidden unless the interval method is changed. You may also need to extend the range of your time dimension to include these extra data points. You can edit the time dimension you are using in your metric set from the explore tab:

and set the range here:

For more information on the missing data rule, please see:

http://www.dundas.com/support/support-center/support-articles/data-visualizations/handling-null-data-points

[adam.short]

That looks very promising, thanks. I'll give it a try.

[adam.short]

I've made the changes you suggested, but unfortunately I'm still not getting the extra months. The missing "data" I'm talking about is in the middle of the range so I don't think extending the range will help. Specifically, what's missing is October from the first image I posted. It appeared when the axis labels were wrong, and it doesn't now they're right, which seems to be how these things go. Thanks for your help so far, it's honestly really appreciated. If there's anything else you can think of that I could try that would be great.

[Inactive-Member-39400045]

Hi Adam,

No problem at all. I was able to replicate the following scenario based on your description of the problem above:

Notice how there is no data present for the months of July and November. At this point, the time dimension levels have been added to the bottom axis in the visualization tab of the data analysis panel, just as we've done previously. In addition, no changes have been made to the default property values of the axis. I recommend you create a new visualization to ensure all the properties are set to their default values.

I went on to apply the missing data rule for the measure like so:

As a result, I can now see the months which were missing previously:

You should not need to make any changes to the axis properties to acheive this result. The missing months should also appear in the data preview window:

Notice how the values for July and November are greyed out. This indicates these values have been generated by the missing data rule.

If you wish to only show the axis label without showing data and leaving the gaps, you can use the following missing data rule configuration:

As a result, we have the following:

Let me know if this solves the problem. If not, please include a screenshot of the current state of your visualization, along with a screenshot of the data analysis panel.