Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sebHan
Contributor III
Contributor III

Combining fiscal year and fiscal month into MonthName function.

Hey!
I have problem combining my Fiscal month data and Fiscal year data into a field, in where I can take the MonthName.

My data looks like this:

sebHan_1-1637660320380.png

 

Where the first of the Fiscal month, is the month nr.

I want a field where when I use the MonthName() function it says

Nov 21, Dec 21, Jan 22 and so on.

 

Can anybody help here?

1 Solution

Accepted Solutions
Or
MVP
MVP

You can pick out the month using Left([Fiscal Month],2) and then use either math or an if() statement to get it to the correct calendar months e.g. if(MonthNum>6,MonthNum-6,MonthNum+6)

You could use the same if() logic to decide which year to pick out, which would be something along the lines of either mid([Fiscal Year],4,4) or '20' & right([Fiscal Year],2) (the latter is to turn it into e.g. 2022 instead of just 22).

You'd then place those two results into a MakeDate statement which would give you an actual date rather than a bunch of text, and you can MonthName that.

View solution in original post

3 Replies
Or
MVP
MVP

MonthName() requires a date value (or a number value which matches a date). You can't apply it to a string. Looks like you'll have to use string functions to break down your text and pick out the appropriate numbers, and then place those into a MakeDate(Year,Month) function which you can then apply MonthName() to.

sebHan
Contributor III
Contributor III
Author

Hey! thanks for the response.

So I'm pretty newbie to Qlik sense, is there a way to do this from my data?

 

Or
MVP
MVP

You can pick out the month using Left([Fiscal Month],2) and then use either math or an if() statement to get it to the correct calendar months e.g. if(MonthNum>6,MonthNum-6,MonthNum+6)

You could use the same if() logic to decide which year to pick out, which would be something along the lines of either mid([Fiscal Year],4,4) or '20' & right([Fiscal Year],2) (the latter is to turn it into e.g. 2022 instead of just 22).

You'd then place those two results into a MakeDate statement which would give you an actual date rather than a bunch of text, and you can MonthName that.