Monthly Reporting on a Period Field Expression

In this post, I'll be showing you how to create some nice reports using the period expression in a text field. This can be because you want to show monthly trends for spend to date, or maybe you want to see on which month the most status red items were raised. These types of reports or very popular because it orders the data in a visually easy to read way and can bring a lot more value to the data. 

The first way I'll show you is the easiest, if you use the format {0:yyyy-MM} in the date field format string in the configuration it will display the period as yyyy-mm, you can then add the _Formatted version of the field to the x value in the report. It will order the report automatically. Here is a link to other ways to format a date field - https://support.softools.net/hc/en-gb/articles/115001074732-Add-a-Format-String-to-a-Date-or-Number

This next method is more complicated than the previous and reaches a similar goal but this way allows for much more customisation to the text that is displayed,

First, you will need to create a date field, this is the date that the reporting will be based off, this can be an input field or an expression run field that is based on when the record was created. After that, you then need to create a text field with the expression below.

if(isValidDate([DateNow]), string(Year([DateNow])) + '-' + if(month([DateNow]) < 10, '0', '') + string(Month([DateNow])), '')

Breaking this expression up it starts off with an if statement checking to see if the date field has a valid date in it using the IsValidDate() function, if true then it runs the next expression if not it then displays nothing.

The next layer of the expression builds the string with the period the date is in. The Year() function pulls back the year of the date and the string() function converts it to string so it can be added to the next piece of string and not added as a number. The month() function pulls back the month the date is in, this pulls it back in the format 1, 2, 3 ,4 not 01, 02, 03, 04 which means that it won't order correctly and will order like 1, 10, 11, 12, 2, 3, 4 etc. To fix this the first part of this next layer checks if the month is less than 10, if it is it then outputs '0' and then adds the month onto that, it not it then outputs nothing and the month on to the rest of the expression. This will produce the period in the format yyyy-mm. You can, of course, play around with this to change the way it's displayed and what you want to be shown.

Then you can now use this field in charts, the example below is of a cumulative matrix chart that is showing the spend to date across 4 months. A key point in the configuration for this is for the period field to be the column field and the order column categories to be ticked.

The last method I'll be showing you is if you want period to be displayed as text and not as a number, i.e. 2019 - 01) January.

You need to create a field that checks what month it is, then constructs the output based on that, below I have added the expression

if(isValidDate([DateActual]), if(month([DateActual]) = 1, string(Year([DateActual])) + ' - ' + '01) January',
if(month([DateActual]) = 2, string(Year([DateActual])) + ' - ' + '02) February',
if(month([DateActual]) = 3, string(Year([DateActual])) + ' - ' + '03) March',
if(month([DateActual]) = 4, string(Year([DateActual])) + ' - ' + '04) April',
if(month([DateActual]) = 5, string(Year([DateActual])) + ' - ' + '05) May',
if(month([DateActual]) = 6, string(Year([DateActual])) + ' - ' + '06) June',
if(month([DateActual]) = 7, string(Year([DateActual])) + ' - ' + '07) July',
if(month([DateActual]) = 8, string(Year([DateActual])) + ' - ' + '08) August',
if(month([DateActual]) = 9, string(Year([DateActual])) + ' - ' + '09) September',
if(month([DateActual]) = 10, string(Year([DateActual])) + ' - ' + '10) October',
if(month([DateActual]) = 11, string(Year([DateActual])) + ' - ' + '11) November',
if(month([DateActual]) = 12, string(Year([DateActual])) + ' - ' + '12) December', '')))))))))))),'')

It first checks if the date is valid and then runs through what month it is and then when it finds its match it finishes the rest of the expression.

Once done you can add the field to the report. Here is an example of what it will look like.

Tip - Always add the year at the front so that the years are grouped as well as months

Tip - Always add the year in otherwise it will group months in different years all under the same month