topic Re: Simple Month Conversion in QlikView

Simple Month Conversion

prees959 — Thu, 23 Jan 2020 16:11:17 GMT

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

Re: Simple Month Conversion

Taoufiq_Zarra — Thu, 23 Jan 2020 21:05:47 GMT

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) )))

Re: Simple Month Conversion

MarcoWedel — Thu, 23 Jan 2020 21:52:19 GMT

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

Re: Simple Month Conversion

MarcoWedel — Mon, 27 Jan 2020 20:58:38 GMT

please close this thread if your question is answered

thanks

regards

Marco

Re: Simple Month Conversion

Brett_Bleess — Wed, 19 Feb 2020 21:02:17 GMT

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