Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculate the difference of months between dates

Hello! I am a beginner in Qlik Sense and would like to calculate the difference between dates as follows:

Pick the highest date recorded for the ID;

Calculate the difference of months with the current date.

ID
DATE (MM/DD/YYYY)
101/10/2017
104/19/2017
105/20/2017
202/10/2017
203/31/2017
209/17/2017


Assuming the current date is 12/4/2017 the expected result would be:


iDMax DateDifference Months
105/20/20176 Months
209/17/20173 Months


Thanks in advance for your attention and help.


Tks

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

Hi,

Exp: =aggr(num((max(Date)-min(Date))/30,'#.##0 Months'),ID)

ss.png

View solution in original post

6 Replies
Anil_Babu_Samineni

Perhaps this?

Dual((Num(Month(Today())) - Num(Month(Max(DATE)))) & '  Months', Num(Month(Today())) - Num(Month(Max(DATE))))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
luismadriz
Specialist
Specialist

HI,

I case you need this in the front end you could do this:

Dimension:

=AGGR(Max(ID),ID)

Measure1:

=Date(Max(TDATE))

Measure2:

=Round((Today()-Date(Max(TDATE)))/30,0.1) &' Months'

You'll need to clarify the algorithm to calculate months.

I hope this helps,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

kaanerisen
Creator III
Creator III

Hi,

Exp: =aggr(num((max(Date)-min(Date))/30,'#.##0 Months'),ID)

ss.png

Anonymous
Not applicable
Author

Excellent tip! Thank you very much.

If the month difference appears as negative, how do I always leave positive?

In some results the information appeared like this:

- 1 Months

Anil_Babu_Samineni

May be look for Fabs() in help.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Thank you! This suggestion met my need. I'll test the others for knowledge. Thank you all.