Interpreting different numeric value types in one column causig problems!

[#1]

Guys,

I have a set of values in one column which are either

1. integers (eg disk memory sizes)
2. percentages
3. times
4. dates

When I try to get an average value using avg(KPIValue) it doesn't seem to interpret the dates or time fielods and gives an answer like 39965 which is something you would see in Excel as a value for a time or date. This is causing obvious problems in my tables and charts which want to be showing times when the valuke is a time etc. has anyone ideas? I was previously using the index function but it doesnt seem to be gettin me anywhere. Any help would be much appreciated.

Regards,

[#2]

Hi

I can't understand what result you would like to see when taking an average of such different kinds of informations.

Do you have other columns in the records that would identify the type of values in your KPI column? If so you might want to distribute the different types of KPI values into individual fields?

Regards

Juerg

[#3]

Unfortunately there isn't a column to separate the types out. In one table there is an avg(KPIValue) which gives the average over time. For the chart expression the expression is simply : KPIValue. There is a KPIDescription Field but this is the name of the KPI and it is not an inicator of the data type.

My attempt to interpret the date element was to put into the expression:

If(Index(KPIValue, '/') > 0 ,date(KPIValue), KPIValue)

. this didnt seem to work though as the values in the expression are still coming out as eg 40000 when it should really be 01/06/2009. Any other possible approaches to this solution whould be a great help

[#4]

I have uploaded a small example to illustrate - this is simplified alot. From the KPIValue table you can see there are different data types eg numeric and then ones that are date format. I have saved two bookmarks in the qvw which illustrate my problem ie the way the KPIValues which are dates are shown in the chart. I tried to create a field in the script but this hasn't made any difference. Any ideas would be great.

Please click on the bookmarks which will describe the problems.

[#5]

I still have problems seeing what you want to achieve.

From your script I see that you try to do correct interpretation of the provided values - but you end up with keeping all of them in the same variable "KPIValueData".

Have you tried to store data in separate fields like

If (Index(Value,':') > 0, time(Value) as KPI_Time

Can your KPIId field be related to the kind of information you get, maybe in another table?

I assume with the data as such you can not create reasonable averages because your assumptions are made on rules that might not match the creation rules for logging the KPI's

Juerg

[#6]

Hi Juerg,

I manipulated my script so that KPIValueData was actually a field where you could choose the type of data, eg Numeric or Tim

I also created a field as mentioned above

If (Index(Value,':') > 0, time(Value) as KPI_Time

.

Then in my chart I have the expression :

if(KPIDataType = 'TIME', KPI_Time, KPIValue)

The time still does not get represented in the correct way however on the y-axis. Not sure of what other steps I can take?

[#7 · Accepted solution]

Hi everyone,

FYI I eventually managed to get this working using a the subfield function:

Previously there were dates such as 22/09/2010 13:15:45 so I created a field with the code:

If(Index(Value, ':') > 0 , time(subfield(Value, ' ', 2), 'hh:mm:ss')) as KPI_Time

This has managed to solve my problem!