Issue with Date Formatting to Subfield on getFieldSelections in Qlik Sense - Need Help

[yh2023]

Hello ,

I am currently facing an issue with date formatting in Qlik Sense and could use some assistance.

The expression I am using is as follows:

date(date#(subfield(date#(getFieldSelections(MyDate), 'MMM-YYYY'), ',', 2), 'MMM-YYYY'), 'YYYY')

Description of the problem:

I have a field called MyDate, which contains date values in the format 'MMM-YYYY' (e.g., Aug-2023).

The first part of the expression, date#(subfield(date#(getFieldSelections(MyDate), 'MMM-YYYY'), ',', 2), 'MMM-YYYY'), successfully extracts the second part of the date (e.g., 'Sep-2023').

However, when I apply the  date() function to this extracted value, it returns NULL, even though it works fine for the first substring (e.g., date(date#(subfield(date#(getFieldSelections(MyDate), 'MMM-YYYY'), ',', 1), 'MMM-YYYY'), 'YYYY')  works).

I have spent a considerable amount of time trying to troubleshoot this issue but haven't found a solution. I would greatly appreciate it if someone could offer their insights or suggestions to help resolve this problem.

[vamshi2i]

Hi Yh2023,

The reason you are getting NULL for the second part is that the 'MMM-YYYY' format used in the date# function cannot interpret month names with more than three characters (e.g., 'Sep' has four characters).

To fix this issue, you need to adjust the date format for the 'MMM-YYYY' representation to handle month names with four characters. You can use the 'MMM-YYYY' format followed by the letter "D" to indicate that the month name can have up to four characters.

try this: 

date(date#(subfield(date#(getFieldSelections(MyDate), 'MMM-YYYY'), ',', 2), 'MMM YYYY D'), 'YYYY')

[yh2023]

Hi @vamshi2i 

Upon replicating the provided code, I can confirm that the expression date#(subfield(date#(getFieldSelections(MyDate), 'MMM-YYYY'), ',', 2), 'MMM YYYY D') indeed returns the second subfield, but when using date() or year() functions to extract the year, it still results in null values.

I would suggest replicating the scenario with the following code:

// Create the inline table with date values
Table:

LOAD * Inline [

Date1

'Jan-2021'
'Feb-2021'
'Mar-2021'
'Apr-2021'
'May-2021'
'Jun-2021'
];

// Format the date as 'MMM-YYYY'
MasterCalendar:
LOAD
date(Date#(Date1, 'MMM-YYYY'), 'MMM-YYYY') AS FormattedDate
RESIDENT Table;

DROP TABLE Table;

Now try to select two fields from MyDate filter you would notice the output is null for the above said expression.

[marcus_sommer]

I suggest to forget this kind of stuff because it's only nasty without being needed. If you want to refer on the year of the selected period you may do it with something like:

sum({< Year = p(Year)>} Value)

[yh2023 · Accepted solution]

I discovered a solution by avoiding the 'MMM-YYYY' format altogether.

Instead, I opted to split the date into separate variables for the month and year.

By using the expression (subfield(getFieldSelections(Myyear), ',', 2)), I extracted the month and stored it as a variable. Later, when the need to compare this variable with another attribute, I relied on this variable for the comparison since direct expression comparison didn't yield the desired results.