Calculate Amount of Months

[jakobjosef]

Hello dear Qlikview-Community, 

I hope you had a good start into 2019.

I have a question concerning the following problem: 

We have conracts with customers, some of the contracts last for 1 month, some for 2 months, 4 months and so on. So the contracts start always onthe first of a month, e.g. 01.12.2018, 01.01.2019 and last until the last day of a month, e.g. 30.04.2019. 

Since the price for the contract is for the entire contract duration, I want to split the amount over the months equally. 

For example: 
Contract from 01.10.2018 until 31.01.2019 for 1.000 €: 250  € for the months October, November, Dezember, January.

To divide the amount correctly, I need the quantity of months. How can I calculate them? So far I used "Round([End-Date] - [Start-Date]/30), but this solution doesnt seem professional to me.

Is there a better solution? I would be glad for any hints. 

Greetings

Jakob

[prieper · Accepted solution]

Build the sum of

YEAR(MyDate) * 12 + NUM(MONTH(MyDate))

and calculate the difference between two dates.

HTH Peter

[jaibau1993 · Accepted solution]

Hi Jakob! I think something like

1+Month(FinalDate)-Month(InitialDate)+12*(Year(FinalDate)-Year(InitialDate))

should work properly.

Bests,

Jaime.

[jaibau1993]

Nice one! But, may it actually be YEAR(MyDate) * 12 + NUM(MONTH(MyDate)) -1 ??

[jakobjosef]

Hi guys, 

thank you so much for your quick hlp, works perfectly!

Jaibau you were very close, it is +1, like:

"End-Date 
-
Start-Date
+
1"

Then everything works fine!

Thank you and greetings
Jakob