Hi sally, you can get the result you want...
...but you will have to jump through a few hoops.
First of all, for each assessment type, you'll need to create a dummy column using something like MIN(0), set mark type as Text, and then drop the assessment Date of Assessment field onto the label shelf. You'll need to do this once for each assessment type. Keep Client ID and Name as rows.
For colour formatting, this calculated field will work, assuming that you're always making the date comparison based on the actual current date (i.e. today):
IF DATEDIFF('year',[Date of assessment A], TODAY()) > 1 THEN "Red"
Drop this onto the color shelf. You'll need to do a separate calculated field for each assessment type.
Workbook attached (v10.4).
PS: i made a couple formatting tweaks to get the table looking as it is, if you have any questions after looking through the workbook, ask away.
Sample assessments_2.twbx 28.6 KB
Could you add the workbook to show how you got to this result?
Thank you for this, One other question, can you move the column title from the bottom to the top?
No, unfortunately for this type of chart, the usual way of moving column headers up top does not work (Analysis -> Table Layout -> Advanced, and unticking the checkbox shown below)
If a client insists, I'll remove the column headers from the chart, and put them back in manually atop the columns using text boxes.