Skip to main content

Hi all,


Sorry for the long-winded explanation but I’m hoping to get my head around the best way to use the chart extension to show project starts for each Quarter. In short, we have a base where each record is a project, and one column nominates a yearly quarter each project to start (ie. “Q1/2021” for projects starting in the first quarter of 2021).


Problem when charting is (1) I can’t seem to arrange the x-axis in the correct sequence, and (2) it doesn’t seem to allow for quarters with no projects starting.


If I sort by ‘view’, it appears to arrange the x-axis by the order in which they appear on the grid:


If I arrange by “X value”, it sorts all Q1 together, then all Q2 together:


If I arrange by “Y value” it sorts by total count for each quarter:


If anyone knows the best way to arrange the data so the X axis is in the right order AND allows for quarters with zero starts, that would be great! Do I need a separate view set up with only that data in the correct order, or can I use the existing view?


Thanks in advance!

AC

Hi Andrew,


re: having the X-axis in the right order

I’m afraid the only options I can think of are:



  1. Set up a separate view like you said with the correct order

  2. Flip the values around so that it’s oYear]/aQuarter] instead of oQuarter]/eYear], i.e. 2020/Q1




re: allowing for quarters with zero starts

Hmm, so in a scenario where there were no project starts in Q2/2020, you would like Q2/2020 to show up, but with no values like so?


If so, I think you’re going to need to create a new table that just has all the quarters and create a count field for it, and use a link field to that table as the Quarters column instead




Here’s a link to the base


Hi Andrew,


re: having the X-axis in the right order

I’m afraid the only options I can think of are:



  1. Set up a separate view like you said with the correct order

  2. Flip the values around so that it’s oYear]/aQuarter] instead of oQuarter]/eYear], i.e. 2020/Q1




re: allowing for quarters with zero starts

Hmm, so in a scenario where there were no project starts in Q2/2020, you would like Q2/2020 to show up, but with no values like so?


If so, I think you’re going to need to create a new table that just has all the quarters and create a count field for it, and use a link field to that table as the Quarters column instead




Here’s a link to the base


Hi Adam,


Thanks for your time with this. I’ve been playing around with your approach and I think your solution ticks the boxes.


Always happy to hear of other ways to achieve the same outcome, but this works well.


Thanks again,

Andrew


Hi Andrew,


re: having the X-axis in the right order

I’m afraid the only options I can think of are:



  1. Set up a separate view like you said with the correct order

  2. Flip the values around so that it’s oYear]/aQuarter] instead of oQuarter]/eYear], i.e. 2020/Q1




re: allowing for quarters with zero starts

Hmm, so in a scenario where there were no project starts in Q2/2020, you would like Q2/2020 to show up, but with no values like so?


If so, I think you’re going to need to create a new table that just has all the quarters and create a count field for it, and use a link field to that table as the Quarters column instead




Here’s a link to the base


Hi Adam - sorry to drag up such an old thread, but is there any way of automating the allocation of records against each quarter in the second tab?

To clarify - hypothetically the tab Table 1 has a column with a calendar date, and then a second column which converts that date to show a quarter (using DATETIME_FORMAT as shown in the two columns below). When I change the date, the quarter automatically adjusts. Can the linked records in the Quarters tab automatically move to the correct row, or will they always have to be updated manually?

Any help appreciated as always! Andrew

 


Reply