Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
ajakash
Participant
0 Kudos
3,316
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:

  1. 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:
5 Comments
Labels in this area