Yes you're right , Feb 27 + 1 month = March 27. I edited that.
My use case is basic; i want to display as a label the names of the 12 months that come before the date of the interview. For ex: interview date is today (4/16/19). I want to show 12 labels starting on the interview date: "April"..."March"..."February"....etc... back to "April"
How about then just setting the day to 1, eg if the interview is 2019-02-27 (Feb 27) then it becomes 2019-02-01 (Feb 1). And then just keep adding (or subtracting) 1 to the month, mod 12 [so that it wraps around], and using a format-date(${newdate}, '%b') to get the month abbreviation ("Jan", "Feb", "Mar",...) for these new dates.
You can manually construct a valid hyphen-separated date string using the concat() function. Specifically
concat(${year}, '-', ${month}, '-', ${day})
However, the trick is that the month and day need to be zero-padded, which regular numbers wont be. So there is some additional work that needs to be done to zero-pad the month when necessary [since we're explicitly setting the day to '01' in this particular case it is already fine].
${month}>=12 is a XPath boolean expression which returns the equivalent of 'true' or false'. You are allowed to mix-n-match XPath sub-expressions of different types - eg booleans, numbers, strings, etc - in the same expression, and XPath will perform the equivalent of type-casting them to the required type of the parent expression; eg using booleans in an arithmetic expression, or strings in a boolean expression, etc. The XPath number() function is basically an explicit operation to convert a boolean subexpression (something producing true or false) to an actual number; in this case true-->1 and false-->0.
So in this example, if the ${month} is less than 12 then the result is 0, so you add 0 to the year (ie same year). Otherwise if the month is 12+ then the result is 1, so you want to increment the year by 1.
Thanks for the solution! but do you know why I still can't convert this formula (from your shared spreadsheet) back into a date? I get the type mismatch error when converting. The formula works fine when all 3 concatenated variables are fixed strings, but not when there's a dynamic variable like "format-date(${date},'%m')" for ${month}
Also, it looks like the formula already accounts for 0-padding, otherwise, the format-date function can return the 0-padding too. So i don't think that's the issue here.
What tool(s) are you using to generate your form? I've loaded the above XLSForm into https://opendatakit.org/xlsform/ and it converts and runs fine.
Yes, format-date() can produce zero-padded values; eg using '%m' it will return "03" for March. The problem is that when you then add 1 to this month value, the arithmetic expression will result in 4, which when (implicitly) converted to a string within the concat function, will result in "4". This is a consequence of how XPath typecasts numbers to strings; from XPath1.0:
A number is converted to a string as follows:
if the number is an integer, the number is represented in decimal form as a Number with no decimal point and no leading zeros, preceded by a minus sign (-) if the number is negative
Hence the need to explicitly zero-pad the newly calculated month number.
The original XLSForm converts fine, but when i try changing the concat formula output from a string to a date, i get the mismatch error type. The formula works when all three variables are fixed strings, but not when any 1 of them is a dynamic variable.
date(concat(${newyear},'-', if(${newmonth}<10,'0',''), ${newmonth}, '-', ${day})) <-- ERROR: type mismatch error
In actuality, there is no specific 'date' type per se; rather, a 'date' is simply a string that conforms to a specific format - "YYYY-MM-DD". How that string came about doesn't actually matter. In this case, the above concat() function generates a conformant date string, which can be used anywhere thereafter as a 'date'. So there is no reason to attempt to convert the concat() result to a 'date' (using date() or any other mechanism for that matter) - the result is already a valid 'date'.
The error you are seeing is a result of attempting to use the date() function, which is used to generate a date given the number of days from a January 1, 1970 epoch. So date() expects to be given an integer, whereas you are giving it a (date) string, hence the type mismatch error.
I see, but the only reason why i'm bringing the string to date type conversion is because I need to only display the month name and year (Apr-19) and not '04-01-2019'. So I was planning on converting that string to a date in order to use the date-format function.
i thought so too, but when converting the form, i get the type mismatch error from this calculation. See attached file, where i added 1 row highlighted in red to your original spreadsheet
This is due to a bug in Validate. I'll try to post a new form with a workaround. In the mean time you can convert your existing form using XLSForm Offline, if you disable the 'ODK Validate' option.
thank you so much! that was immensely helpful, and i've learned a lot about ODK date calculations. I had no idea you could use the coalesce function in ODK too.
YW. Sorry it took so many iterations to get finally right
coalesce() is one of the more obscure/esoteric XPath functions you normally don't run across, but it occasionally has its uses (like this workaround). More typically you might use it to, say, convert null responses to 0 when adding up totals; eg
${a} + ${b} + ${c}
If some of the questions are optional (ie null value) then the above calculation wouldn't work (you'd get "NaN"). Instead you would write