I have an accountingperiod field, which is formated YYYYMM. For example 202405 would be May 2024. This is currently stored as an integer.
How can i transform this accountingperiod field into a date field, so i can filter visuals by rolling date based on this field (which is a monthly period), opposed to manually changing the filter when needed.
Hi @HarveyB-B - There are two ways to approach this.
Approach1: When preparing your dataset in QuickSight: 1. Add the accountingperiod field 2. Click on the field name to open the field properties 3. Under Field transformation, select Date/Time 4. Specify the format as YYYYMM This will transform the integer field into a date field that you can then use for filtering visuals. Doing the transformation at the dataset level makes the new date field available for use across your analysis.
Approach2: 1. Create a calculated field with this formula: DATE(LEFT(accountingperiod, 4), RIGHT(accountingperiod,2), 1) This will extract the year and month parts from the integer, and sets the day to the 1st of the month. 2. Use this new calculated date field to filter your visuals. As you change the filter period, it will automatically update based on the monthly periods in the calculated field. So in summary, the key is to use QuickSight’s DATE function to transform the integer into a proper date that you can then use for filtering.
Hi there, thanks for the fast response.
I’m not too sure how to do approach 1, so i tried approach 2.
This was the result i got:
It looks like the DATE function you tried does not exist?
@HarveyB-B The advice from @awsvig is right. But you have take his example from approach 2 as is or may be did a copy and paste.
Typically when you reference your fields in Calculation Field you have flower brackets around the field name. See an example below where I have highlighted the field name.
You are correct. “parseDate” is the function.
Additionally, for approach 1, please find example screenshots that i captured from a dataset that i had. “Datakey” column is similar to your “accounting period” column.
Hi again, i tried approach 1, but it didn’t work as expected, it matched it to another date field and applied the day in month as the same, when realistically i want it to be the last date in month. So if an invoicedate is 16/05/2024, your approach1 would set this new field to 16/05/2024, when i would want it to be set to 31/05/2024 (end of accountingperiod)
For approach 2, this is the error i get:
Again, the accountingperiod field is formatted YYYYMM, but as an integer field. What i need is to turn this into YYYYMMDD, as a date field, and have the DD as the last day of the month.q
I have done a long way around this, however struggling to parse as a date.
This is the calculated field i have created and for now named ‘temp’
However when i try to parse it as a date i get this error:
What is this error?
I changed it to the following:
But i get this response:
And as you can see its a string format in the correct format (YYYYMMDD)
Breakdown of the solution:
Max date for each accountingperiod
I then formatted this as an actual date (concat to include ‘/’ between YYYY/MM/DD)
I then parsed this field as a date:
Hi @HarveyB-B,
If you’re looking for an easier solution to get the last date of the month, try this:
parseDate(toString({accountperiod}), 'yyyy/MM/dd')
Use the addDateTime function to add 1 month to get the first day of the next month.
Use the addDateTime function to subtract 1 day to get the last day of the current month.