Simple Month Conversion

[prees959]

HI,

I have a table with a Month Year field as follows:

Mar 19
Apr 19
Feb 18
Jan 20
etc
etc

I need to be able to find the number of months between that date field and the current month?  Is there a simple expression to achieve this please?

Thanks

Phil

[Taoufiq_Zarra]

maye be

=(year(today())*12+num(month(today())))-(year(MakeDate(num('20'&right(Yourfield,2)), num(Month(Date#(left(Yourfield,3), 'MMM'))), 1) )*12+num(month(MakeDate(num('20'&right(Yourfield,2)), num(Month(Date#(left(Yourfield,3), 'MMM'))), 1) )))

[MarcoWedel]

Hi,

one solution might be:

example

tabMonths:
LOAD *,
     Month(Today())-Month(MonthYear)+(Year(Today())-Year(MonthYear))*12 as MonthDiff;
LOAD Date#(MonthYear,'MMM YY') as MonthYear 
Inline [
MonthYear
Mar 19
Apr 19
Feb 18
Jan 20
];

hope this helps

regards

Marco

[MarcoWedel]

please close this thread if your question is answered

thanks

regards

Marco

[Brett_Bleess]

Phil, did either of the posts help you with the use case?  If so, per the last post, please be sure to return to the thread and on any post that helped, use the Accept as Solution button, which will mark that post as the solution giving the poster credit, and that also lets other Members know what worked too.  We appreciate members that follow-through on their threads and close them out.  If you came up with a different solution, you can post that and then mark it after you post it, and if you still require assistance, leave an update.

The only other thing I can offer is the following Design Blog post that may help, you could convert these into the numeric equivalents and subtract those to get the number too potentially, and this is pretty much where Marco went with things, but the following may provide some help in understanding the underlying functionality.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

Regards,
Brett