Technical Articles
Solution to find the Number of Days in a Month, without using Date Field in SAP HANA Modeler by using HANA Calculation Views
Introduction:
In this blog, I am going to explain how we achieved calculating the number of days in a month, without using the date field in the HANA Calculation view, by using a simple formula. This was a requirement from one of our customers and we had achieved it only by using the month field.
Main Problem:
In HANA Modeler, we have the following formula to find the number of days in a month or last day in a month, if the date field is available.
lastday(“date_field”)
But in case if the date field is not available, what could be the solution? This blog shows the solution here with a simple formula.
Solution:
To achieve the solution, the below steps need to be followed:
- Convert the Year field to an integer, by using the below formula in a Calculated Column:
int(“year”)
2. In this step, we can see how the number of days in February can be derived for both Leap and Non-Leap Years. I have defined two formulae here as follows in the Calculated Column:
Formula 1:
if(((“Year_int” % 100)=0 AND (“Year_int” % 400)=0) OR (“Year_int” % 4 = 0 ),’29’,’28’)
Refer to the screenshot below:
Formula 2: if(“Year_int” % 100 = 0,if(“Year_int” % 400=0,29,28),if(“Year_int” % 4=0,29,28))
Refer to the screenshot below:
3. And here goes the final step. Use the below formula in the Calculated Column, as shown in the screenshot:
Formula: if(“month”=’01’or “month”=’03’or “month”=’05’or “month”=’07’or “month”=’08’or “month”=’10’or “month”=’12’,31,(if(“month”=’02’,”Leap_Feb”,30)))
In the above formula, the month field should contain values from ‘01’ till ‘12’.
The values that appear in the Data Preview is shown below against few example periods:
Conclusion:
So, we have got the number of days in a month without using the date field and I hope this blog should have clearly made you understand the steps and formulae involved to achieve this. For further information on why I used the formulae in this blog, kindly check the link in the Reference section. Feel free to post your comments if you have any other ideas to achieve this.
Reference:
- https://www.wikihow.com/Calculate-Leap-Years#:~:text=Check%20if%20the%20number%20is,it%20is%20a%20leap%20year.&text=For%20example%2C%201900%20is%20evenly,you%20a%20result%20of%204.75.
Wouldn't be much simpler to generate the date out of concatenation of year,month and 01 as day and then using lastday function?
Dear Konrad,
Thanks. That's really a good idea. But in my case, it didn't work out. I've tried the same before the current approach and I got an error as mentioned below:
The Calculated Column that I have used
The Error That I got
You can let me know where I would have gone wrong, if you can identify the issue.
Anyway, many thanks for suggesting this.
Regards,
Akash.
I think this might be some issue with your data set. Make sure that in month column you have only values from 01 to 12. Error message indicates that there is some issue when trying to convert string to date,
I checked this formula and it works as expected.
I have tried above two cases, but did not work.
I have a data as shown in below image.
I have created a calculated column with the expression as shown below.
I am getting an error as shown in below screenshot.
Can you please, let me know where I would have gone wrong, if you can identify the issue.
Thanks in Advance.
Dear Pavan,
Sorry, that I missed to notice your problem earlier. This seems to be a problem with the datatype of the columns you have used in the formula.
Is this issue already already sorted out?
Thanks,
Akash AJ