Overlaying multiple lines in a Line Chart

Question

Hi. I'm attempting to produce a Line Chart where values from multiple years are overlaid on a single "year" horizontal axis. The data in this case is simply the number of employees per payroll over several years.

When I plot the employee counts (vertical) versus full payroll date (horizontal), I get a single wiggly line covering all the years. This is all well and good.

However, when I use only the 'mm/dd' part of the Check Date for the horizontal axis, and then use the Check Year as a Vary-Color-By (Horizontal) element, I get only colored dot trails for each year's data instead of contiguous lines. Notably, for a section where one year's data has no other year's data between two of its points, I get a line segment (i.e. two data points connected, then the next pair, and so on.)

I've been unable to find any control to ensure that each year's differently colored data forms contiguous lines instead of a dotted track. Can this be done? Obviously, each year's pay dates are on different specific dates within each year.

Additionally, is there a way to ensure that the horizontal "year" axis always extends fully from 01/01 through 12/31 even when there are no data points extending to those extremes?

Thanks you,

Mike

Note: I'm a client of Paychex FLEX which uses Oracle Business Intelligence 12.2.1.3.0 for their 'Custom Reports'.

Gianni Ceresa · Answer

Hi,

You should look for any gap in your data and also at how you "use only the 'mm/dd' part" of your date.

If your data provide a contiguous set for all the days of the year, the chart will plot a proper line, if there is a granularity issue or gaps, the line can break or be just dots.

Ignore the chart and focus on your dataset, that's the one that must be clean and cover all your days properly (as real data, not because of a formatting mask on your date).

If you simply display the date as 'mm/dd' it isn't going to really work because you aren't really using a 'mm/dd' column, you only have the impression of it visually. You will need a formula doing that work for you to have really only 'mm/dd' as a text value (because it can't be a date anymore).