Chart showing week by week data

Nick shared this question 50 days ago
Answered

Hi,

I would like to create a chart having the number of changes on y axis against each week on the x axis.

Ideally, I would like the week to start from Monday and display as: 11/10/2021, 18/10/2021

How can this be achieved?

I have set the starting week as Monday in Admin > Config > Time and Region settings, and also tried to plot a time series chart (continuous, fill in zero for null values) but faced the below issues:

- it displays the date of the starting week as being a Thursday

- Over a 1 yr time frame, it displays the dates fortnightly

Anything I am missing here?

Or any other way I can do this? I've tried using the week calculated field but that also doesn't work in that it only display the week number, and not the actual date.

Thanks,

Nick

Comments (11)

photo
1

Hi,

Please refer to the attached line chart.

It seems the date on the tooltip is ok, but are we able to change the date on the y-axis so that it shows the date for every monday?

Thanks,

Nick

photo
1

Hi Nick,


There are limitations with the formatting of values on the x-axis when they are a time series chart. Axis labels are automatically created and spaced, so there is currently no formatting available to get the result you are after. There is an existing idea post that someone has raised here, where I will be updating that task when Developers assess the issue. I've added your details to the existing Enhancement Task.


Let me know if you have any further questions.


Kind regards,

Simon

photo
1

Hi Nick,


I'm just messaging to let you know that I will be marking this as answered for the moment. Please follow the existing idea post here for future updates.


Kind regards,

Simon

photo
1

Hi Simon,

Thanks for your assistance, and sorry for my delayed response.

In another use case, let's say we are charting the data for the whole year of 2021 and the first data point is in Feb. In this case, the chart starts from Feb, instead of showing the zero data points from 1st Jan all the way to the first data point.

Is it possible to add this to the idea?

Thanks,

Nick

photo
1

Hi Simon,

Attached screenshot showing the above.

I think it's just a cosmetic issue where the line is not drawn from (0,0) to the first data point (6/6/2021).

Chart is from 1/1/2015.

Thanks,

Nick

photo
1

Hi Nick,


Thanks for your message.


Currently, as far as I'm aware, the way that time-series works is that it interpolates data. What you are asking for here would be an extrapolation. The way that I see it in this example, is that the data would actually start from 2015-01-01, not from January as you are requesting. To rephrase, I think being able to set a start and endpoint for the x-axis on time series data is a more viable request. Let me know if you agree with this.


A workaround to address this in your case might be to create a Virtual Table with a list of dates and then join that to the View. This way when you use those dates in your Reports, you would have valid dates with a null value for the data points that you are trying to plot and you would see the full time range. This wouldn't allow you to set specific start and endpoints, however, it would show you the full range of the filter.


Let me know if this makes sense, I've also gone ahead and added it to the task.


Kind regards,

Simon

photo
1

Thanks Simon.

This bit is a bit confusing:

"The way that I see it in this example, is that the data would actually start from 2015-01-01, not from January as you are requesting"

I think you meant data would actually start from June if I understand correctly?

For the workaround though, do you have an example you could share by any chance? Off the top of my head, are you are suggesting to create a fixed start and end date, and join this to the existing view ? But then, how are we to report on 1 date field which would include the fixed start/end date and the ones coming from the db?

Thanks,

Nick

photo
1

Hi Nick,


In your example, your filter is from 2015-01-01 to the current date, meaning that there would be NULL data shown on your chart all the way back from 2015-01-01.


There are several references online on how to create a date/ calendar table. Here is one approach.


You would perform a left join on the calendar table to the data table(s) on date = date, where if there is no date in the data table(s) it will still populate for that date with a NULL value. The calendar date field is the date field you would use in Reports.


Let me know if this is clear.


Kind regards,

Simon

photo
1

Hi Simon,

With "In your example, your filter is from 2015-01-01 to the current date, meaning that there would be NULL data shown on your chart all the way back from 2015-01-01."

By interpolation, I guess you are saying it will display a chart between the 2 min/max data points that is not NULL.

ie. instead of drawing a line from 0,0 being 2015-01-01, it will start drawing a line from the next data point that is no NULL.

Is that correct?

Thanks for the links/suggestions.

Regards,

Nick

photo
1

Hi Nick,


That is exactly right. This is the way that the workaround will work, and assumedly the Enhancement Request.


Let me know if this is all clear, and I can go ahead and close this question, where I will continue correspondence on the active idea task.


Kind regards,

Simon

photo
1

Thanks for the assistance, Simon.

No further questions at this stage.

Cheers,

Nick