Convert text to numeric in cross-tab

[lolu]

I have a cross-tab showing analytical results that are stored in text format in the database. This worked fine until I needed to make some calculations like Avg and StdDev, that shall also be shown in the cross-tab.

To complicate everything the user can set a boolean parameter to hide some results if they have a surtain flag set to true. To accomplish this i used the following formula:

If ({PV.questionMark} = 1 and {?showMv} = true) then
""
else if
(isnull({PV.formattedValue}) = true) then
""
else
{PV.formattedValue}

Now I need to convert the results to numeric format, to use as input in the cross-tab, but in all formulas I've tried, I get 0 (zero) instead of null in the fields that has been set to "" in the formula above, which results in errors in the calculations.

How shall I write a formula to get null in the numeric fields that are empty?

Thanks
/LO

 

[GJParker]

It's not completely clear here what and where you are trying to convert your data but remember NULL means the absence of any value and is not the same as "" or 0.

secondly any calculations that encounter an unhandled null value will cause an error i.e.

{FieldA} * {FieldB} would cause an error if either field were null.

HTH





Gary Parker
MIS Data Analyst
Manchester, England

 

[lolu]

Well, that's the problem, I can't figure out where it's best to make the conversion so I've tried several formulas. This was one of them(@numValue):

EvaluateAfter({@formattedValue});  
If ({PV.questionMark} = 0) then
ToNumber({@formattedValue})

where {@formattedValue} is the formula in my first posting

I then use @numValue in the cross-tab but then I get 0.0 in the empty cells in the cross-tab and that is then used when the Avg is calculated.

It would be nice to be able to do the conversion directly in @formattedValue e.g. something like:

If ({PV.questionMark} = 1 and {?showMv} = true) then
""
else if
(isnull({PV.formattedValue}) = true)  then
"" 
else
ToNumber{PV.formattedValue}

but of course it's not possible to mix data types and you can't write ToNumber(""), so I'm stuck...

Here's the problem illustrated:

I have this input (text):
2.0 3.0
2.2 2.6
?2.5 2.4 (? is in a separate column)

I want this (numeric):
2.0 3.0
2.2 2.6
2.4
Avg 2.1 2.7

but I get this (numeric):
2.0 3.0
2.2 2.6
0.0 2.4
Avg 1.4 2.7

/LO

 

[lbass]

What version of CR are you using? What are your column and row fields?

-LB

 

[lolu]

I use CR 10. The row and column fields are ObjectID(string) and relative time (numeric).

/LO