MakeDate Doesnt work as expected

[sogloqlik]

Hi All,

I'm pulling data from SAP system. In the load process, there is a date field (Posting date) being created with makedate using the fields FISC_YEAR and Period (See file attached).

the command is:

MakeDate(FISC_YEAR, if(Right(Period,2)>12,12,Right(Period,2)))

for most of the records this new column contains a date format but for the other records it brings the date as a number. if I activate Date on "Posting date" it shows the date. (There are records like these for all the months).

The data in the columns (FISC_YEAR) and Period looks the same for all the records.

We are using QS Feb2022

What can be the issue?

 Regards.

[LRuCelver]

I am unable to repliacte what you are describing.

Your problem may be related to parsing strings as integers. (Right() returns a string that is used as the integer for the month) This is just a theory since the formula you provided worked fine for me.

[sogloqlik]

Hi LRucelver,

I share your thoughts. I dont think you can replicate the issue from the attached file because it may have something to do with the way  SAP system represent the data. for me the  question now is in which cases MakeDate will turn date into a number (Since MakeDate returns dual, I assume the number is the code and the date is the presentation) - and for that you need to know the code behind the function.

Regards.

[Or]

MakeDate() does indeed return a dual - all numbers and dates are duals in Qlik. There isn't really anything to know here, though - it just creates a date with the year, month, and day parameters supplied. If the result isn't a valid date, it'll return null. If it is displaying the numeric value of the date, odds are this is because of something you've done overtop the field - for example, formatting it explicitly as a number, placing it in an aggregation function such as min() or max(), etc.

[sogloqlik]

Hi MVP,

I dont think this is the issue because the formula i presented in the original question is the only one and the data arrives in one bulk from the same source table. The function acts differentlly for different rows in the same bulk.

Regards,

[stevedark]

Hi @sogloqlik 

You should always wrap a MakeDate statement with a Date statement to format it to how you want it to appear:

Date(MakeDate(2024,2,26), 'DD MMM YYYY')

If you are getting different results on different rows that implies to me that you may be mixing the Month and the Day field? Things work correctly when the day of the month is less than or equal to 12 (but gives the wrong date) and if the day of the month is over 12 it fails.

The fact that you have the logic if(Right(Period,2)>12,12, makes me think you are already trying to deal with that?

Saying that, looking at your spreadsheet I think it is just that you need to add the Date function, as =date(44896) gives 12/1/2022.

Steve

[sogloqlik]

Hi Stevedark,

thank you for your answer. there is no day field so i wouldnt assume there is a mix in the data and it also doesnt work for periods less than 12 (The condition is set to handle financial periods). I shared the column date(posting_date) to show that it fixed the issue. My conclusion is that the problem is in the source data and if something changes there, it would not work again so a better understanding of the MakeDate mechanism may help solve te issue.

Regards.