Calculating week-of-year for a date

Though it is easier for me to use if function than lookup table function :slightly_smiling_face:

Thanks for sharing this with detail explanation. I tested the xml and it works fine. But while trying to convert the xls to XML it returns an error.

Hmm... dunno. I exported both the XLS and XML - from the original Kobo form - just fine. I'll poke around, thank you for the heads-up.

1 Like

Hi Gareth,

i think the issue on converting to xml is a bug of the Validate probably. (it fails on validate the "decimal-date-time(concat(${year}, "-01-01"))" )
Running XLSform offline 1.8.1 unchecking the option to Validate the Xform will produce an xml that then works on Collect.

I understand you got it from Kobo, but this is an Open Data Kit forum and not a Kobo one.
It is a very interesting form and a good showcase, but it is important also to show something that works for users that don't use Kobo and that face problems on converting the xlsform through the usual way (XLSform Offline or online).

@ARIF_AZAD_KHAN if you want to use the form editing according to your need, then at the moment you need to use the XLSform offline and uncheck the Validate option.

I'll open a new thread on the Support Channel and try to address an issue to GitHub.

@Xiphware thanks a lot for the useful example (I'll use it with my students)

2 Likes

Sorry. I tested the XML in Collect (and XLS via Enketo preview), but alas not actually loading into Aggregate which would have triggered the Validate bug(s). :slightly_frowning_face: I'll edit the form to workaround; decimal-date-time() is arguably optional since ODK's XPath eval will do the same conversion automatically, so the form should still work OK without it.

Hi @Xiphware, I didn't talk of Aggregate. Try to upload your xls on http://opendatakit.org/xlsform/ and you'll see what I mean.
I opened a post on support on the issue:

I've replaced decimal-date-time() in the form(s) with number() in the interim whist the Validate bug is fixed [number() performs same conversion]. And renamed the form to avoid the other bug [that filename was just last minute when I put form files into posting. D'oh]. Tested against http://opendatakit.org/xlsform/

@Xiphware attached xls does not give expected result in ODK collect but works fine in enketo.

It looks like there's some bugs lurking around decimal-date-time() support. I've pulled the forms till I can figure out a common workaround that'll work against both Collect, Validate, and Enketo... sorry folks.

After determining the bug in Validate, I've been able to formulate a workaround and re-posted a new XLS and XML form, which have been tested against both Validate, Enketo, and Collect.

For those interested, I've highlighted the change in the XLS form that was necessary to 'fake out' Validate and prevent it from throwing a bogus type mismatch error when evaluating date-related XPath expressions. Specifically, I changed

dayofyear = decimal-date-time(${date}) - decimal-date-time(concat(${year}, "-01-01"))

to

dayofyear = decimal-date-time(${date}) - decimal-date-time(concat(coalesce(${year},"2019"), "-01-01"))

Replacing ${year} with coalesce(${year},"2019") means Validate gets a valid date string when it evaluates the decimal-date-time() argument during its (static) type checking phase; the 2019 year will be ignored as soon as you run the form and enter an actual date. This is only needed as bug workaround and is not part of the algorithm.

1 Like

Hi @Xiphware
I was trying to use these code in kobo server but do not operate well.
Thanks.

Hi @Xiphware
I was trying to use these codes in my form but do not operate well. See the attached xls form
Thanks.
Godlove
Malariaregister2.xls (54.5 KB)

Your form loads into KoboToolbox (and XLSForm Online for that matter) and runs fine under Enketo, and appear to be producing the correct results:

(although before you enter a date it will show garbage instead). What do you specificially mean by "do not operate well" ?

Hi @Xiphware
Many thanks for the strong support you provide on this, but I was trying to apply these codes on my xls form and when I was trying to convert it into XML form it seems that there is no problem. But during data exportation, some variables do not show any values as weekday and next follows.
See the attached xls form with there dataset after exporting below
Malaria register2 - latest version - labels - 2019-08-20-19-19-44.xlsx (6.4 KB) Malariaregister2.xls (53 KB)

Yes in kobo offline/online work properly but in odk collect do shows weekdaynum and weekofyear.

Can you please go back and try to run my original weekyear.xls (above), to make sure that works for you. This has the validate workaround, and was tested to work correctly under both Collect and Enketo.

Okay, Let me do that

Dear @Xiphware
I am already understanding where the problem occurs, this is when I was trying to use odk in Swahili language does not support some of functionally like weekday number and week-year.

Hi @Xiphware
I am new to the ODK forum. I think the xls is perfect and works well for a normal calendar year.

I have tried to change this based on your xls (above) to accomodate fiscal year but unable to get the correct results, where the fiscal year starts on 1 October and ends on 30 Sep each year.

Can you please assist. Your help will be much appreaciated.

That will be somewhat dependent on what your definition is of a 'fiscal year week'... eg is October 1 defined as week 0 vs week 1? Further, is, say, Oct 3 always in the same 'fiscal week' as October 1, or does it depend of what actual day-of week it happens to be relative to October 1? eg say Oct 1 is Saturday; is Monday October 3 in the same fiscal week or the next one? And what if October 1 is Sunday? etc.

Hi @Xiphware

Thank you for your response

  • The start of October 1 would be defined as week 1
  • Sunday would be the start of any fiscal week.
  • Oct 3 will always be dependent on what actual day of week it happens to be relative to October 1
    I think in some years there will be 53 weeks.

Based on your above technical questions that is all info i can provide.

Hope this info helps.