Statistics cannot be performed on calculated value in Excel using OData

1. What is the problem? Be very detailed.
Hi. The problem is that I have a calculated value, let's call it A, from three previous inputs, X,Y,Z. When I try to analyze the data using the OData feed, I am able to see all of the data and perform statistics on X,Y,Z, however, I am unable to perform any statistics on the calculated value A.

I am using ODK Central v0.6 on a Dell Latitude 5590 with several different types of cell phones; Huawai, Samsung, etc.

3. What you have you tried to fix the problem?
I have tried to create read only values that use the default as the calculated A value to no avail. I have tried changing the type from 'calculate' to 'hidden'. I am looking for a way to convert the calculated value into a float or at least an integer - Odata is interpreting it as a string (maybe).

4. What steps can we take to reproduce the problem?
I have taken a screenshot of a similar example.

When exporting as a csv file, I do not encounter this problem as I am able to perform statistics on the calculated A value.

Thank you for the help.

In your Excel file columns [F] and [G] are formatted as a text. There are a lot of fixes the one of them is multiplying that column to 1. See below: image

Hi @Odil. Thanks for this. Do you know if there is anyway to fix this problem when creating the form (as opposed to adding another column or two to the table)? For me, this solution is great, but I am giving access to the data to others who would prefer to easy be able to stream the data directly into Excel without any modifications.

I think you're right that OData is treating the calculate field as string. I think this actually happens as part of the conversion from XLSForm to XForm/XML: I think any calculate field in the XLSForm will have a type of string in the XForm, even if the calculation will always return an int or something else other than a string.

There's an existing pyxform issue/discussion about this here. It seems like XLSForm would need to change slightly to account for this. There is a potential XLSForm workaround mentioned in the issue, though I'm not sure how robust it is.

ODK Central basically just looks to the XForm type to determine the OData type, so I don't think any ODK Central changes would be needed around this.

Note that while OData is typed, the columns of a CSV file are not typed. I believe that when Excel sees a value in a CSV file that looks like a number, it simply treats it like a number (unless you instruct it to do otherwise).

Hope this helps!

Hi Matthew. I will definitely be interested in seeing this problem fixed as we are performing further calculations on these values.

@Matthew_White just to follow back on this, I did the fix from the pyxform discussion and it seems to be working just fine.

For others: The fix is to create another column in your Survey tab called "bind::type" and put "decimal" in the row in which the error was occurring.

1 Like