tag:blogger.com,1999:blog-5585680429157536914.post1119766687368080685..comments2023-12-19T01:06:46.450+00:00Comments on QlikView Addict: Dates in QlikView - Part 2Matt Fryerhttp://www.blogger.com/profile/16375702764551893303noreply@blogger.comBlogger22125tag:blogger.com,1999:blog-5585680429157536914.post-20587776362465612122018-07-25T11:22:38.448+01:002018-07-25T11:22:38.448+01:00Hi,
Sep 26 2017 12:00AM I have this format how to ...Hi,<br />Sep 26 2017 12:00AM I have this format how to convert to MMM YYYY?<br />Kind Regards,LATHIKhttps://www.blogger.com/profile/01017803219088671651noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-45300671101212173962018-03-28T09:40:43.566+01:002018-03-28T09:40:43.566+01:00Can this no.(3.2241087962902) convert into Total m...Can this no.(3.2241087962902) convert into Total minutes<br /><br />This no. shows 03 Days 05 Hours 22 minutes and 43 Seconds<br />Rakesh Kumarhttps://www.blogger.com/profile/00225754519337592362noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-20680922701731333632017-12-18T10:55:45.940+00:002017-12-18T10:55:45.940+00:00how convet date hexadecimal in qlikview
how convet date hexadecimal in qlikview <br />Anonymoushttps://www.blogger.com/profile/09242743557639608248noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-10931166560297420632017-08-09T12:58:13.935+01:002017-08-09T12:58:13.935+01:00That worked, thanks a lot! That worked, thanks a lot! Mhttps://www.blogger.com/profile/15463987183204606880noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-66202082754951438602017-08-09T11:07:01.267+01:002017-08-09T11:07:01.267+01:00Try the format 'M/D/YYYY h:m:s TT'
Regard...Try the format 'M/D/YYYY h:m:s TT'<br /><br />Regards<br />MattMatt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-61897898391072325362017-08-09T07:28:05.128+01:002017-08-09T07:28:05.128+01:00Hi,
I have a timestamp string with the format ...Hi, <br />I have a timestamp string with the format '7/18/2017 8:11:59 AM', how do I convert it using the timestamp# function?<br />RegardsMhttps://www.blogger.com/profile/15463987183204606880noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-89694039485424569822017-08-07T12:35:02.392+01:002017-08-07T12:35:02.392+01:00goodgoodJens C. Krusehttps://www.blogger.com/profile/06928934543082018334noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-49793134588792667662017-05-09T10:57:07.771+01:002017-05-09T10:57:07.771+01:00Hi Ali
Try the right() function!
Regards
MattHi Ali<br />Try the right() function!<br /><br />Regards<br />MattMatt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-65967685464639415662017-05-08T09:43:30.049+01:002017-05-08T09:43:30.049+01:00Hello,
Please help in the below Query
i have a f...Hello,<br /><br />Please help in the below Query<br /><br />i have a field with [Actual 2013] <br /> i need to take Year from the above field<br /><br />Thanks in advance<br /><br />Regards<br />AliAnonymoushttps://www.blogger.com/profile/04231231966359260341noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-47962800811685290192017-03-29T11:01:53.021+01:002017-03-29T11:01:53.021+01:00But what if I have a text format - '2016 W01&#...But what if I have a text format - '2016 W01'. How can I convert it to a date format? I need 2 separate fields: Year and Week (in 'W01' format)NMhttps://www.blogger.com/profile/00434305351246710215noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-24846025879697871072016-05-17T14:31:31.383+01:002016-05-17T14:31:31.383+01:00Hi
I don't believe there are any inbuilt funct...Hi<br />I don't believe there are any inbuilt functions to convert from a Gregorian calendar to a Persian calendar. The simplest method would be to use a conversion table loaded in and connected to the date field of your choice. Using the dual function you could still store a numeric "days since epoch" but display the Persian date making them sort correctly etc.<br /><br />Regards<br />MattMatt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-57927140626299182282016-05-17T05:59:13.681+01:002016-05-17T05:59:13.681+01:00Hi
can we convert the system date to persian date ...Hi<br />can we convert the system date to persian date in qlikviw<br />QlikTeamhttps://www.blogger.com/profile/04759280485962904597noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-5059565494875108592016-02-17T03:13:00.989+00:002016-02-17T03:13:00.989+00:00Hey Matt
I've just working with Qlik (and scri...Hey Matt<br />I've just working with Qlik (and scripting in fact) so pardon the simplistic question.<br /><br />I need to set a starting date at a specific date/time. I want to store this in a variable so I can generate a master calendar based on today's date.<br /><br />Here's how I have defined the mindate variable:<br />Set vMinDate='01/01/2016';<br /><br />In a temp cal table (with autogenerate, the std code), I do this<br />date($(vMinDate)+RowNo()-1) as TempDate<br /><br />I can't seem to get it to recognise the string as a date.<br /><br />Thanks for any help!Timbohttps://www.blogger.com/profile/07929933758766018016noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-64135291875809301002015-10-30T16:23:12.511+00:002015-10-30T16:23:12.511+00:00Hi
I am trying to load data from a SQL server us...Hi <br />I am trying to load data from a SQL server using a OLE DB connection and would like to convert the data in the columns suffixed with the word date from YYYYMMDD to DD/MM/YYYY format. Please Help.<br />Self-learning Qlikview Newbie <br />The script I am using is<br />SQL SELECT AccountingYearMonth as AccountingPeriod,<br /> AdmiDate AS StartDate,<br /> Age,<br /> SpellDisDate AS DischargeDate,<br /> SpellDominantProcedure AS DominantProcedureSpell,<br /> SpellLoS AS LoS,<br />FROM SLAM.dbo."1415SLAMTAS";<br />Unknownhttps://www.blogger.com/profile/05151383387932810098noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-4991890804564742082015-09-04T22:13:11.401+01:002015-09-04T22:13:11.401+01:00Tanks!Tanks!Wesleyhttps://www.blogger.com/profile/12449882292513072423noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-67845120648123493682015-08-03T11:36:35.241+01:002015-08-03T11:36:35.241+01:00Hi Leroy
There are several answers to your questio...Hi Leroy<br />There are several answers to your question, and choosing which is the correct for you will depend upon your exact requirement. Assuming both dates are being stored as true dates then subtracting one from the other will give you the difference in days. Dividing this number by 365 will give you an approximation.<br /><br />floor((DateField1 - DateField2) / 365)<br /><br />Assuming you are doing this in an expression and not the script then it should make no difference that the dates are in separate tables. This method doesn't account for leap yours though thus why I said there are several ways depending on what you wish to achieve. <br /><br />Regards<br />MattMatt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-83287676968853096032015-07-30T17:59:18.941+01:002015-07-30T17:59:18.941+01:00Hey,
Can anybody please tell me how to calculate ...Hey,<br /><br />Can anybody please tell me how to calculate an age of a person with respect to a "certain date".<br /><br />I mean....If the BirthDate field has 01/28/1970 & and the present date (i.e. Today's Date) is 7/30/2015. But I want to know the age of a Person when the CertainDate field is 03/14/2004.<br /><br />P.S.- What should be the calculation keeping in mind that the CertainDate is in a different related table other than the BirthDate.LEROY RULZhttps://www.blogger.com/profile/07238576979578564956noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-21152144359392002802014-08-12T16:27:28.590+01:002014-08-12T16:27:28.590+01:00Hello,
After reading through your post I feel you...Hello,<br /><br />After reading through your post I feel you could be able to help me with the issue I'm facing.<br /><br />I load data from an excel source which has a key date field. A dashboard requires to display the information in the Q1-2012, Q2-2012 format. The problem is our company's financial year is from April - March, so the Q1 should begin from April.<br /><br />I tried a few options but failed to get it to meet my needs. Will you be able to suggest a method of getting the QQ-YYYY format?<br /><br />Any help is much appreciated.<br /><br />Thanks!GayanJhttps://www.blogger.com/profile/08989907397191224538noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-36744064112689805812013-02-15T17:56:36.481+00:002013-02-15T17:56:36.481+00:00Hi
Part 3 is still being written but should be re...Hi<br /><br />Part 3 is still being written but should be released in the next few days. I've the outlines of parts 4 and 5 also so they will follow at some point.<br /><br />As Michael mentioned above you can calculate the number of months between 2 dates using the following formula:<br /><br />=((year(FirstDate)*12)+month(FirstDate)) - (((year(SecondDate)*12)+month(SecondDate)))<br /><br />And you can use the same method and substitute multiplying the year by 52 (number of weeks a year) and using the week() function instead of the month() function.<br /><br />Hope that helps<br />Matt Matt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-64397596470403237412013-02-15T10:30:11.117+00:002013-02-15T10:30:11.117+00:00Nice one. Matt is the part-3 is released or writin...Nice one. Matt is the part-3 is released or writing in progress ? Intrested in knowing how to find months/weeks etc between 2 dates.Anonymoushttps://www.blogger.com/profile/18056058130432023678noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-4124439961005907262012-08-16T12:35:49.904+01:002012-08-16T12:35:49.904+01:00Hi Michael
I'm glad you found it of use and y...Hi Michael<br /><br />I'm glad you found it of use and you've happened to guess some of the topics for part 3.<br /><br />I looked at calculating the number of days between 2 dates in part 1. This is simple, just subtract one true date from another.<br /><br />Calculating the months between 2 dates is a little more tricky however. As you've obviously worked out, unfortunately there is no DateDiff function in QlikView as there is in SQL and VBScript, although it is on the wish list for a future release. The method you mentioned is the one I normally use and in part 3 I'll be covering a number of topics around dates including their use in variables. As part of this I'll show how to create a "MonthDiff" function using a variable with passable parameters as a replacement for a proper DateDiff.<br /> <br />Calculating the number of years between 2 dates in the same way as DateDiff would is simple as follows:<br />=year(today())-year(DateField)<br />Again, one thing I will touch on in part 3 is how there can be more than one definition of the logic behind what we consider to be a year having passed. For example, does 365 days have to have passed between 2 dates or just the calendar year change. Watch this space :)<br /><br /><br />Regards<br />MattMatt Fryerhttps://www.blogger.com/profile/16375702764551893303noreply@blogger.comtag:blogger.com,1999:blog-5585680429157536914.post-9286064439813221232012-08-15T16:11:58.028+01:002012-08-15T16:11:58.028+01:00This is awesome! Thank you for laying it out so cl...This is awesome! Thank you for laying it out so clear and concise! Have you considered covering the ever interesting how many days/months/years between two dates? <br /><br />This was one approach I read about on the forums <br />=((year(today(2))*12)+month(today(2))) - (((year([Set up date])*12)+month([Set up date]))) <br /><br />for how many months between two dates.<br /><br />From here http://community.qlikview.com/thread/47580Michaelhttps://www.blogger.com/profile/11206707906777002583noreply@blogger.com