Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) |
---|---|
1 | 01/10/2017 |
1 | 04/19/2017 |
1 | 05/20/2017 |
2 | 02/10/2017 |
2 | 03/31/2017 |
2 | 09/17/2017 |
Assuming the current date is 12/4/2017 the expected result would be:
iD | Max Date | Difference Months |
---|---|---|
1 | 05/20/2017 | 6 Months |
2 | 09/17/2017 | 3 Months |
Thanks in advance for your attention and help.
Tks
Perhaps this?
Dual((Num(Month(Today())) - Num(Month(Max(DATE)))) & ' Months', Num(Month(Today())) - Num(Month(Max(DATE))))
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
Hi,
Exp: =aggr(num((max(Date)-min(Date))/30,'#.##0 Months'),ID)
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
May be look for Fabs() in help.
Thank you! This suggestion met my need. I'll test the others for knowledge. Thank you all.