Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jakobjosef
Creator II
Creator II

Calculate Amount of Months

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

2 Solutions

Accepted Solutions
prieper
Master II
Master II

Build the sum of

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

and calculate the difference between two dates.

 

HTH Peter

View solution in original post

jaibau1993
Partner
Partner

Hi Jakob! I think something like

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

should work properly.

Bests,

Jaime.

View solution in original post

4 Replies
prieper
Master II
Master II

Build the sum of

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

and calculate the difference between two dates.

 

HTH Peter

View solution in original post

jaibau1993
Partner
Partner

Hi Jakob! I think something like

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

should work properly.

Bests,

Jaime.

View solution in original post

jaibau1993
Partner
Partner

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

jakobjosef
Creator II
Creator II
Author

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