Can anyone help me to use time in calculations?

[#1]

Hi,

I am tryin to calculate average productivity per minute.

for example, an employee ships 15 parcels in 90 minutes, his "productivity" is one shipment per 6 minutes.

Everything works perfectly, until the minutes exceed 1,000.

The data I am importing shows minutes worked in minutes and seconds, e.g 455:30 is 455 minutes and 30 seconds. 

My expression  TIME SPENT / UNITS shipped gave a stange result ,but I fixed it by multiplying the TIME SPENT by 24. Now everything is accurate EXCEPT when the minutes are greater than 1,000 in which case  I get a "no data to display message"

I am sure that ther emust be a simple solution but I have been unable to find it yet.

Thanks for your help.

Steve

[swuehl]

I assume that you have an issue with interpreting your thousand separator, assuming that your minutes field is formatted like '1,000'. Or I have misunderstood your posting. How do you read in the fields and how do you transform your fields then?

Could you post your load statement used here in the forum or a small sample app?

[#3]

Here is an example depicting the same situation:

From NHL .com statistics

When selecting a player from the player list such as Anthony Stewart, ES TOI (even stregth Time on Ice 596 minutes everything works perfectly. I see the productivity (minutes of ice time per goal scored and effectiveness minutes of ice time per goal allowed.

the expresions I use are = [ES TOI]*24/ESGF   and [ES TOI] *24 / ESGA

When I select a player like Brandon Sutter who has 1196 minutes of ice time ÈS TOI] the boxes with the calculated fileds indicate that there is no data to display.

I appreciate of your assistance.

Best regards,

Steve

Message was edited by: stevenmorein ( fixed typos)

[qliksus]

thats bcoz more than 1000 values have comma so treated as text use this expression

PurgeChar([ES TOI],',')*24 / ESGA

[swuehl · Accepted solution]

Steve,

I think my assumption was correct, that there is a problem with number parsing.

If you look at the table viewer or e.g. in a table box, you'll notice that some records for TOI or ES TOI are formatted differently (left aligned), indicating that they were read in as text, not as number. These records have a thousand separator ',' in their minutes value, like '1,234:56'

If you want to do some calculations, you need to read all records in as numbers. Unfortunately, QV won't recognize a time format with a thousand separator.

But you can just remove the comma using purgechar() function:

LOAD

     Player,

     Team,

     Pos,

     GP,

     purgechar([ES TOI],',') as [ES TOI],

     [ES TOI/G],

     [SH TOI],

     [SH TOI/G],

     [PP TOI],

     [PP TOI/G],

     purgechar(TOI,',') as TOI,

     [TOI/G],

     Shifts,

     [TOI/S],

     [Sft/G],

     F17,

     F18

FROM

[http://www.nhl.com/ice/playerstats.htm?fetchKey=20122CARSASALL&sort=timeOnIce&viewName=timeOnIce]

(html, codepage is 1252, embedded labels, table is [2011-2012 - Regular Season - Carolina Hurricanes - Skater - Time On Ice - Total Time On Ice], filters(

Remove(Row, Pos(Top, 2)),

Remove(Col, Pos(Top, 1))

Hope this helps,

Stefan

[#6]

Problem solved, Purging the "," (thousand separator) wored like a charm.

Your assistance is greatlky appreciated.

Steve