No of months Count

[kkkumar82]

Hi all,

I am struggling with a small requirement, attached a small piece of data, in that there are two yellow back grounded columns which need to be calculated.

every project has a start month and end month, I want two new columns like number of months, avg Savings

for eg:

Project ID, StartMonth, EndMonth,Savings, countofmonths,Avgsavings

101,            Jan 17     ,  Apr 17     , 4000    ,  4                     , 1000

count of months is derived as Jan, Feb ,Mar, Apr(count) and AvgSavings as Savings/Countofmonths ie 4000/4 = 1000

Hope I am clear

[sunny_talwar]

Have you seen this to calculate month difference?

How Do I Get the Number of Months Between Two Dates?

[kkkumar82]

So to use this should I convert my start months to first date of that month and End month to last date of that month?

[sunny_talwar]

You don't have to... as long as you don't want dates to have any impact on the count of month... the expression should handle everything for you

[kkkumar82]

Just checked the post you gave I think it doesn't take into account the dates , I think I can use it

thanks for the stress on that post

[kkkumar82]

Sunny can we bring in the number of rows equivalent to the count of Months

for eg

Project ID, StartMonth, EndMonth,Savings, countofmonths,Avgsavings

101,            Jan 17     ,  Apr 17     , 4000    ,  4                     , 1000

Desired

Project ID, StartMonth, EndMonth,Savings, countofmonths,Avgsavings, Month

101,            Jan 17     ,  Apr 17     , 4000    ,  4                     , 1000         , Jan17

101,            Jan 17     ,  Apr 17     , 4000    ,  4                     , 1000         , Feb17

101,            Jan 17     ,  Apr 17     , 4000    ,  4                     , 1000         , Mar17

101,            Jan 17     ,  Apr 17     , 4000    ,  4                     , 1000         , Apr17

[sunny_talwar]

Sure, you can use a while loop to do this...

Loops in the Script

[kkkumar82]

Thanks sunny for the post, I will take from here ..