TrnExportCSV Trend Export Averaging Issue

What version of Citect are you using?

What mode number are you using for the TrnExportCSV() command? You many need to add mode 4: Ignore any invalid or gated values.

You could try using the TrnExportCSVMulti() function from the Toolbox. It works similarly to TrnExportCSV(), but is not the same.

You could also try using TrendReader to view the trend data files. I'm curious if the '1' values are actually 'N/A' (invalid) or 'Gated'

You could also try sending the trend files to Aveva support for them to check.

Eric, aka The TrendMaster, the site in question is using v7.40 SP2. 

Thanks to your Trend Reader tool I can see the problem - a data sample logged as 1.#QNAN

I'm not sure if this points to a problem in Citect or in the IO Device.  I wonder if it could be possible to mask this issue by incorporating an expression into the trend tag definition rather than just logging the variable tag.  

I am using Mode=1 in TrnExportCSV().  I did try it with Mode=5, but all that did was output a blank cell instead of an n/a cell, so unfortunately Citect didn't just ignore the invalid value and calculate the mean value for the period using only the good values as I had hoped it would. 

Do you think that TrnExportCSVMulti() would be a chance of behaving differently with this issue?  

1.#QNAN indicates the value is not a valid floating point number (Quiet Not A Number). The PLC may be creating that intentionally because of a math error (such as divide by zero) or a sensor failure. There are millions of possible combinations of bits that result in a NAN floating point value. Citect does use 2 of them to indicate a trend sample is N/A or GATED. I don't believe Citect checks for any other NAN values.

Since doing mathematical calculations on NAN values is supposed to cause the result to be NAN, I think that is causing the trend server's Min/Max/Average calculations to fail. Since TrnExportCSV() and my TrnExportCSVMulti() both call TrnGetTable() to get the calculated samples from the trend server, this problem would probably affect both functions. You could use either export function with the sample rate that the trend records at, like 1 second. Then, you'd have to eliminate the bad sample and do the averaging yourself.

I think the real solution is to avoid recording the bad value. If you can check for the bad value in the PLC, you could set the register Citect reads to the last good value. Or, as you suggested, use a Cicode function in the trend expression to change the QNAN value to Citect's N/A (not available). For example:

Trend Expression: QNanToNA(MyTag1)

Cicode function:

//Convert trend sample NAN values to N/A for recording
REAL FUNCTION QNanToNA(REAL rSample)
  IF rSample = rSample THEN //NAN values fail equality checks
    RETURN rSample;
  ELSE
    RETURN TrnGetInvalidValue();
  END
END

I had trouble creating QNAN values in Citect, so I'm not sure if this function will work. Please let me know how it works for you.

1.#QNAN indicates the value is not a valid floating point number (Quiet Not A Number). The PLC may be creating that intentionally because of a math error (such as divide by zero) or a sensor failure. There are millions of possible combinations of bits that result in a NAN floating point value. Citect does use 2 of them to indicate a trend sample is N/A or GATED. I don't believe Citect checks for any other NAN values.

Since doing mathematical calculations on NAN values is supposed to cause the result to be NAN, I think that is causing the trend server's Min/Max/Average calculations to fail. Since TrnExportCSV() and my TrnExportCSVMulti() both call TrnGetTable() to get the calculated samples from the trend server, this problem would probably affect both functions. You could use either export function with the sample rate that the trend records at, like 1 second. Then, you'd have to eliminate the bad sample and do the averaging yourself.

I think the real solution is to avoid recording the bad value. If you can check for the bad value in the PLC, you could set the register Citect reads to the last good value. Or, as you suggested, use a Cicode function in the trend expression to change the QNAN value to Citect's N/A (not available). For example:

Trend Expression: QNanToNA(MyTag1)

Cicode function:

//Convert trend sample NAN values to N/A for recording
REAL FUNCTION QNanToNA(REAL rSample)
  IF rSample = rSample THEN //NAN values fail equality checks
    RETURN rSample;
  ELSE
    RETURN TrnGetInvalidValue();
  END
END

I had trouble creating QNAN values in Citect, so I'm not sure if this function will work. Please let me know how it works for you.

Just reporting back with the good news that adding your function to the Trend Expressions of the problematic tags has eliminated this QNAN problem. 

For completeness I also tried different combinations of TrnExport functions and modes within those functions, but as you suggested could be the case, none were effective until the QNANs were eliminated from the recorded data. 

Thanks very much for your help.  

Great! Glad to hear it worked.