Difference in Month

[beck_bakytbek]

Hi Folks,

i have a situation and it does look like (see attached screenshot)

for instance:

ID      FromDate           ToDate      Amount      Difference (days)       Amount / Difference

4       20.03.2017      31.07.2017         272               133                         2,05

my question are:

1, how to bild from: Difference (day) = Difference (month) : for examlpe > 133 days = 4 months, and Amount / Difference  from

2,05 into 68.

2. how can i bild or implement from Difference (month) in Master Calendar,

my issue is: if i select instead of 31.07.2017 the date 31.06.2017 i have to show 3 months, and my amount is equal to 90,67

i want to buil the filter like: Jan,Feb,Mrz,Apr and if i select the Months, i want to show the change of my anount.

Thanks a lot in advance for your help and suggestions

Beck

[swuehl · Accepted solution]

Have a look at

Calculating Months difference between two dates

(also at the comment section)

Some People estimate the month difference also by dividing day difference by 30.5

= Floor ((Max(ToDate) - Min(FromDate) ) / 30.5)

// alternatively use Round / Ceil function

// Replace Max() Min() functions with whatever you Need to get your date difference in your context

[beck_bakytbek]

Hi Stefan,

Thanks a lot for you feedback and help, i try it and will report you if i have any problems

Beck

[OmarBenSalem]

Maybe sthing like this Beck?

load *, Amount/DiffMonth as NewAmount;

;

load *, round(DifferenceDays/30) as DiffMonth,

(year(to)*12+month(to))-(year(from)*12+month(from)) as newdifference

;

load *,date(date#(FromDate,'DD/MM/YYYY')) as from,

date(date#(ToDate,'DD/MM/YYYY')) as to

inline [

FromDate ,      ToDate  ,    Amount ,    DifferenceDays,      Amount / Difference

20/03/2017 ,    31/07/2017,        272 ,              133  ,                      2,05

];

You can calculate the difference btwn the 2 dates directly in months

or calculated manually approximatively from the number of days

[beck_bakytbek]

Omar Ahi,

Thanks a lot for your feedback and Help

Beck