How to create a dummy variable grouping dates into quarters?

Hi @Violeta ,
Try tagging your answer with Custom Field Creation: https://www.qualtrics.com/support/survey-platform/data-and-analysis-module/data/add-new-fields/manual-variables/#TaggingMultipleResponses

Let me know if it helps

Hi @dxconnamnguyen,

Thank you for the heads up.

Yes, it probably would work, as it works with a hidden FY-Q variable that could be manually updated. Still, I was looking for a more automated way to do it in advance - e.g. setting rules which quarter which period includes.

Did you find a solution for same ? I am also in need of this.

Try the solution mentioned here.

You could make an Excel spreadsheet that has all the possible dates and corresponding quarters next to it. Then, you could import it as recoded values into the dashboard settings. Instructions here on importing recoded values:

https://www.qualtrics.com/support/vocalize/dashboard-settings-cx/dashboard-data/recoding-values/#ImportRecodes

I liked this option. The only downside is it can have maximum of 5000 recode values, so this solution will work for 13 years :) However, for already existing data, I can download the dates and recode for them and for future alone, I shall set all dates. Thanks a ton.

Hi @Violeta ,

Adding on to Shashi’s suggestion…

Set 2 variable (year and year2) if instead of Jan, you start your financial year at a different month (Let’s say you start in Nov)

Then, set condition 1 for 1st quarter to be if month = 11 or 12 or 1 like the screenshot below.

If it falls into this condition, set Quarter (Quart) = 01 and Financial Year (FY) = year2.

Next, set condition 2 for 2nd quarter (refer to screenshot).

If it falls into this condition, set Quarter (Quart) = 01 and Financial Year (FY) = year.

Similar steps as 2nd quarter for 3rd and 4th quarter.

In Data & Analysis’ field editor, create a column using combine. You can get both year and quarter inside a column (example, 2024Q3 or 2024-3 or 3-2024) by changing the delimiter.

Note: You can also amend the Quart value to be something else like Q01 or Q1.

@Chee Heng_SZ, thanks a lot!

I followed your instructions, but the three embedded fields (moth, year and FY-Q) are still empty. Please see the screenshots below.

Additionally, I have a question regarding the ongoing surveys that will continue running in 2025, 2026, and beyond, as well as the historical data going back before FY2022. Is using ${date://CurrentDate/Y} problematic for setting the fiscal year in this context?

Would the following method be correct for labeling historical data?

FY2023: ${date://CurrentDate/Y/-1 year}
FY2022: ${date://CurrentDate/Y/-2 years}, etc.

Hi @Violeta ,

oh, that time&date is not necessary. I forgot to remove it after test.

I use OtherDate instead of CurrentDate when trying to use + or - day/month/year.

The method mentioned above is for new records as the branch only works in a survey and not existing records.

For current existing records, you probably have to filter the dates between quarters, followed by “batch edit” manually to add FY and quarter number into their respective columns before the FY-Q combine column.

Thank you, @Chee Heng_SZ.

Yes, I'll follow your advice for the existing records, but my expectation was that the columns for month, year, and FY-Q would be populated for newly submitted surveys.

I received a survey response about half an hour ago, right after creating these embedded fields, but the 3 columns are still empty for this new record as well. Do you have any idea why this might be happening? 

Hi @Violeta ,

I am not too sure.

You might want to 

1. Check if there is any typo in the survey flow.
2. Re-publish the survey after the edit.
3. Ongoing survey session will likely not have the amended portion. Only new survey attempts after re-publishing will have the amended portion.

Hope this help.

Sorry, i forgot about the different year, hence updating that Q1 branch condition to something like below.

This will take FY to be 2026 if month is 11 or 12 but remain FY to be 2025 if month is 01 if current date is in 2025.

Thanks a lot, @Chee Heng_SZ!

I'm not sure how to update the branch logic considering this is an ongoing, large-scale survey that will continue running in the future.

Should I replace ${date://CurrentDate/Y} with ${date://OtherDate/Y} when defining the embedded field for the year?

Then, when setting the embedded data below the branch, should I write each exact year (e.g., ${e://Field/2024}, ${e://Field/2025}, ${e://Field/2026}, etc.) instead of ${e://Field/year}?

Currently, it's set up as follows:

Currently I see this in the data columns. My expectation for the FY-Q was that instead of 03 it will be 2024Q03 considering how I set it (please see the second screen shot below).

Hi @Violeta ,

CurrentDate is for the current time period. If you wish to have other periods, you will need to use OtherDate.

Those ${} is indicating that they are piped text or in some other cases math operations.

In your screenshot, the FY value is missing some colons ":", hence, the year and year2 are not working properly.

You can use the insert piped text function to pipe in embedded data ${e://Field/year}.

I don't think these "${e://Field/2024}, ${e://Field/2025}, ${e://Field/2026}" will work unless you have define embedded data "2024", "2025" and "2026".

Hey there,

Thanks to @Chee Heng_SZ’s support it all works as expected.

However, I’m encountering another issue: I need just one 'FY-Q'  field for all data sources within the same dashboard to serve as a filter. Unfortunately, while the field created following the above-described steps is of Text Value type, the 'FY-Q' field for all other data sources is Text Set type. Is there a workaround to convert the 'FY-Q' field for all other sources to Text Value type, ensuring consistency across all fields?

Many thanks in advance!

Hi @Violeta ,

You can change the field type to your desired type at the field editor or when while mapping dashboard data.

More information:

Field editor

Dashboard, understand fields

Hi @Chee Heng_SZ.

Thank you. I’m aware that, in general, I can change field types. However, in this case, I cannot change the newly created derived 'FY-Q' (it’s greyed out; please see the screenshot below):

That’s why I tried to change it for the rest of the data sources/surveys from Text Set type to Text Value type to ensure compatible.

After making the changes as shown in the screenshot below,

→

now that this field is of Text Value type, it’s completely unusable as a filter (which was the purpose of this whole exercise).

So, I’m still looking for any workaround to use ‘FY-Q’ as a filter for all data sources in the same dashboard. Specifically, as a first step, I need to somehow convert the derived FY-Q from Text Value type to Text Set type… Do you have an idea how this could happen?

Hi @Violeta ,

In the dashboard data mapping, you can click on the text value/text set type and change it to the other types.

Thank you, @Chee Heng_SZ.

I tried to do it in the dashboard data mapping, but this is the result:

Do you have other suggestions how could resolve this?

Many thanks!

Hi @Violeta ,

Combining Fields (qualtrics.com)

Qtip: Concatenated fields are always set to the “text value” type, even if you are using recode values. This cannot be changed, even when mapping the field to a CX Dashboard.

It seems like it can’t be done.

...now that this field is of Text Value type, it’s completely unusable as a filter (which was the purpose of this whole exercise).

Can you elaborate a little in what way it is unusable?

Thank you, @Chee Heng_SZ.

I was afraid that's the situation. The same applies to two more derived fields that I need to use as filters in my dashboard, which were created with bucketing:

Here is what I mean with '’...now that this field is of Text Value type, it’s completely unusable as a filter (which was the purpose of this whole exercise).'’ - I cannot map the field as Text Set type (shared the error message in my previous post) and if I set this field to Text Value type for all five data sources/surveys, it's just an empty text box, i.e. again, I cannot filter data by FY-Q.

In short, I have a dashboard with five data sources/surveys, two of which are large-scale, receiving over 500 responses per week. For all of them, I need the 'FY-Q' and 'Product' fields to be set as Text Set type and used as filters in the dashboard. For example, I would like to see records from all five surveys concerning Product A in 2024Q1.

However, for these two large-scale surveys, the derived 'FY-Q' and 'Product' fields are currently set as Text Value type, and there is no option to change them to Text Set type, even when mapping the field to a CX Dashboard...

Is there any workaround for this? 

Thanks in advance!

Hi @Violeta ,

My apologies, I just learned something new which may be useful. 

An alternative to get “FY-Q” as text set data is to change how you get to that information.

1. In survey flow, use 1 embedded data instead of two embedded data to combined in field editor later. 

Note: this only affect new response data.

2. Batch edit existing records to manually add in values for the new embedded data FY-Q. (Not sure if it is advisable/good practice)

As for the other “Product” field, i am unsure how you get/determine the value. Perhaps it can be done similarly to the above.

Thanks a lot, @Chee Heng_SZ. No need to apologize, I owe you.

I followed your advice and I am now populating the new 'FY-Q' field with back data following these instructions: https://www.qualtrics.com/support/survey-platform/data-and-analysis-module/data/add-new-fields/manual-variables/#TaggingMultipleResponses. However, I cannot see the Edit icon so as to be able to add field values for multiple responses.

I'm very sorry to bother you with this, but do you know where I can find this icon (so as to add values for hundreds of records per quarter at once)?

Many thanks!

Hi @Violeta ,

You should be able to see the edit icon after you select the responses that require edit.

Note: i tried batch edit of 100 response at a time, 5 times if you wish to edit 500 responses. I am unsure if you will be able to batch edit more than 100 responses at a time.

Hi @Chee Heng_SZ,

Yes, correct - it works now. Thanks a lot!

The only remaining issue is that 'FY-Q' is not populated correctly from now on (please, see the screen shot below - it 'takes' just the quarter #). Most probably something in the branch logic was not properly set..?

  Thanks in advance for your suggestions how to update this!