InfoQube IM - Community

Submitted by jan_rifkinson on 2010/11/20 16:43

I read thru the documentation & it was very clear but needed more detail for dummies like me.

Here's what I want to do for Project cost

I have worker hours & cost set up this way

Fields =

date

time in (24 hr format)

time out (24 hr format)

total hrs

rate

cost

so as I understand it I would need the following equations

total hrs = time out - time in

Equation for Field Total Hours might be something like =sum(time out-time in)

Equation for cost might something like =sum(total hrs*35)

I saw there were certain named function, i.e. sum, max, min, ave

but

what I don't know is what other terms are available, i.e. +, -, *, >, <, =>, =<

and

the format for the equation.

Eventually I will add a total project cost based on a number of items of cost but that will need to be a column sum of all the costs

If someone can help me with this I'd appreciate it.

bumped 2010/11/29

Comments

I dunno will this help Jan (I dont even know was this in default file or did someone do the dirty work for me at some stage long ago)

I have a cost field which has this equation ('xx' being your hourly rate):

= ZN([duration] * xx)

The duration field, mentioned in the above, has this equation:

WorkTime( StartDate, EndDate , 8) - ( NonBillable)

the 8 in the duration equation is something to di with if it goes from one date to the next - without time included - it's considered 8 hours work - but I've never actually put that to the test.
'NonBillable' being a field numbering the hours not charged for
I notice formatting is not good - spaces in the wrong places - but it still works...

I dont know could the first one bypass the duration field, e.g. thus:

= ZN([StartDate, EndDate] * xx)

in your case this could (possibly!) be (I still avoid spaces in field names in case..):

FieldTotalHours

(TimeIn, TimeOut)

and the Cost field ('xx' being your hourly rate):

= ZN([FieldTotalHours] * xx)

ZN is not defined in the Equations page so I'm not sure what it is....

Field Management Dialog - Equations

Tom, Thanks very much for your reply.

I tried to duplicate your equation (I assume Row equation) without luck.

I understood some of the logic of the equation but other parts did not computer for me

I'm still wondering if there are arithmetic functions that I can use, i.e. +,-,*,/

some of the syntax didn't make sense to me unless there were other definitions.

For example =ZN([StartDate, EndDate] * xx assumes that there is a formula somewhere that explains the difference between "Start & End" Dates.

In my formula since I was inputting time in 24 hour format, I assumed if I could subtract start from finish I would get # of hours but to no avail.

I know I'm missing something here

[quote=jan_rifkinson]

Tom, Thanks very much for your reply.

I tried to duplicate your equation (I assume Row equation) without luck.

I understood some of the logic of the equation but other parts did not computer for me

I'm still wondering if there are arithmetic functions that I can use, i.e. +,-,*,/

some of the syntax didn't make sense to me unless there were other definitions.

For example =ZN([StartDate, EndDate] * xx assumes that there is a formula somewhere that explains the difference between "Start & End" Dates.

In my formula since I was inputting time in 24 hour format, I assumed if I could subtract start from finish I would get # of hours but to no avail.

I know I'm missing something here

[/quote]

Yes, sorry Jan, I left that little detail out ! -

StartDate and EndDate are date/time fields in my file

So,

StartDate, EndDate

is the same as EndDate minus StartDate (in hours - this is obviously more complex if a time is not given in these fields - which I mentioned above)

which is why I thought you could apply that to your file using the relevant field names (TimeIn, TimeOut) - or - (Time In, Time Out) or whatever !

They say a picture is worth a 1k in words so

which going by my suggestion above would be:

~~=Time_TotalHours (Time_Arrival, Time_Departure)~~

EDIT/ of course I'm wrong on first attempt - the field is Time_TotalHours, so there's no point in including it's own name in the equation

Try:

= (Time_Arrival, Time_Departure)

Sorry Tom. This is what I don't get

All my fields are numbers, not text type fields so they look like the following:

Time_Arrival = 0935

Time_Departure=1425

It seems to me all 3 fields have to be formatted as time

is that

or is it

My next question is: what does the comma between the time fields in your formula represent?

How does IQ know I want the difference between the two values?

I appreciate your help here & I'm sorry to keep peppering you w more questions but I'm missing something.

I'm sorry Jan

what I've been describing above is under equations but in the Auto-assignment box - *not* in the row equations

But I also presumed your time fields would be 'date' fields so it wouldnt have worked anyways cause of that -

the number field doesnt understand 60 minutes - your example above

Time_Arrival = 0935

Time_Departure=1425

would give a time of 0490 which doesnt actually mean anything

You would have to use a date field - I dont know can you convert a number field to a date field - do you have lots of items filled for these fields ?

Hi Jan,

Were you finally able to do something ?

Hi Armando,

Short answer: Nope

Ok Jan.

Today is a somewhat busy day, but I might be able to find some time to explain how to it on Monday... if you're willing to wait until then.

It's not complicated at all, but I need to phrase it properly, etc.

Of course I can wait. Thanks for your interest.

Hi Jan,

As Tom suggested, you'll have to first convert your "time in" and "time out" fields to date fields. Is that possible ?

If you already have data in there, you'll have to create 2 new fieds and manually transfert/convert your data to the new field. Then erase the old fields. There might be other ways to automate that transfer using some formatting functions etc., but ... I'll wait for you answer before I go further.

================

P.S. : what I can tell you right away is that the built in IQ "WorkTime" function could probably be used for what you want. You could probably copy the "Invoices" and "WorkDone" grids from the sample DB -- they basically do the same thing that you want to do.

Personally I'd start with the WorkDone grid, and just add a few columns from the Invoices grid : HrsRate, SubTotal, Taxes, Total.

You'd then be pretty much set... If you have your own DB, you could just copy paste the equations in your own field, just making sure that the fields in the equations correspond to your own (the right names). You also have to make sure that your fields are set to the right types (date when dates, number when numbers, etc.)

[EDIT : added the "taxes" field to the ones I'd add tp the WorkDone grid]

Armando, with all due respect, did you look @ the screen dump that I posted?

[quote=jan_rifkinson]

did you look @ the screen dump that I posted?

[/quote]

I just saw it - I can tell you how to make it work but hopefully Armando or Others will help with the theory (see red text!)

1) First The Date_Time_Total field has to be a number field

2) The equation here has to go in the Row Equation as you initially had it (- for some reason, as I said above, the cost calculation in my grid went in the Auto-Assignment box - why?)

3) = doesnt work in the equation (why?)

4) this worked for me:

WorkTime(Date_Time_In , Date_Time_Out , 8)

the 8 calculates eight hours per day if you just put dates in there (I'm not sure what would otherwise happen

Why does WorkTime have to go in front ?

5) EDIT/ I dont know does auto-update have to be ticked - anyone?

Hi Jan,

Sorry... I did look at it when you first posted it, and then I forgot yesterday.

But it doesn't change anything to the second part of what I said, since your fields are already date fields now (from your screenshots, it seems like it).

If I was you, I'd just copy the model from the sample DB and / OR follow what Tom suggested

Here (I rearranged his suggestions) :

Step 1

Date_Time_Total :

- has to be a number field

- WorkTime(Date_Time_In , Date_Time_Out , 8) equation has to go in the Row Equation section

Step2

Cost :

- = Date_Time_Total * Rate equation foes in the Row equation section of the cost field.

Try it... This should work.

=============

Some answers to Tom's questions :

> the 8 calculates eight hours per day if you just put dates in there (I'm not sure what would otherwise happen

the parameter just sets the limit to 8.

I.e. : You wouldn't be able to have more than 8 consecutive hours in the same day.

That number could be set to 24... But that wouldn't work well for multiple days of work, of course. You'd have to set another parameter to ajust the value accordingly, or set the function accordingly.

> 3) = doesnt work in the equation (why?)

Yes, it should actually. You can put the "=" sign or not, it shouldn't change anything. Generally I put it when the equation isn't a function.

> Why does WorkTime have to go in front ?

Because WorkTime() is a function... And that,s how the syntax has been established in many programming languages.

> EDIT/ I dont know does auto-update have to be ticked - anyone?

Autoupdate doesn't need to be checked : it's only important if the value of a field would change with each passing day (i.e. : Current time is a parameter in the calculation)

[quote=Armando]Some answers to Tom's questions :

> the 8 calculates eight hours per day if you just put dates in there (I'm not sure what would otherwise happen

the parameter just sets the limit to 8.

I.e. : You wouldn't be able to have more than 8 consecutive hours in the same day.

> 3) = doesnt work in the equation (why?)

Yes, it should actually. You can put the "=" sign or not, it shouldn't change anything. Generally I put it when the equation isn't a function.

> Why does WorkTime have to go in front ?

Because WorkTime() is a function... And that,s how the syntax has been established in many programming languages.

> EDIT/ I dont know does auto-update have to be ticked - anyone?

Autoupdate doesn't need to be checked : it's only important if the value of a field would change with each passing day (i.e. : Current time is a parameter in the calculation)

[/quote]

thanks for those answers Armando

Clarification:

the "=" didnt work for me when I didnt have the WorkTime( ) bit included (when included you dont need the "=")

EDIT/ I updated the manual page with this example Field Management Dialog - Equations

[quote=Armando]

Here (I rearranged his suggestions) :

Step 1

Date_Time_Total :

- has to be a number field

- WorkTime(Date_Time_In , Date_Time_Out , 8) equation has to go in the Row Equation section

Step2

Cost :

- = Date_Time_Total * Rate equation foes in the Row equation section of the cost field.

Try it... This should work.

[/quote]

Armando, thanks for your input

The cost calculation MAY be working <$0>

but the hrs calculation doesn't seem to be functioning as expected

Hi Jan,

You have an equation for DateTimeTotal : =DataTimeTotal(DateTimeIn, DateTimeOut,8)

The DateTimeTotal in bold, must be a VB Script function. The stock IQBase uses the WorkTime function. Checkout the VB Script for this function.

Also, naming a function the same name as a field hasn't been tested, so I recommend against it.

HTH.

p.s. Give me a call if you want...

Pierre,

I got WorkTime computation working

now

I tried to calculate cost with

Cost(WorkTime* Rate)

which yielded nothing

"Cost" & "Rate" are both number fields

Eventually I want to add in the number of "Workers" so I thought I would have something along these lines

Cost((WorkTime* Rate)*Workers)

but

obviously I need to get Cost working first

What am I missing here?

Is there another "magic word" for my cost field?

Is there a complete list of built in function words like "WorkTime" ?

When I'm finished with this process, I'm going to write some notes on the experience

Thanks.

Hi Jan,

Why don't you just copy my suggestions above ? I took the time to detail the process. Pierre didn't describe all the steps.

Just copy the equations as I wrote them and paste them as described in my post. Everything should work, providing you didn't change your field names and types (dates, numbers...) In the mean time -- from your post above it seems like you did, but who knows.

Thanks.

P.S. : Cost(WorkTime* Rate) doesn't work because Cost() isn't a function.

P.S.S. : I think we should all try not to multiply advices without reading what other proposed but build on what has already been proposed. What I proposed works.

Armando, Are you a forum moderator?Just askin'

So... did you try what I proposed ?

[quote=jan_rifkinson]

Armando, Are you a forum moderator?Just askin'

[/quote]

I guess you could say that.

[quote=Armando]

So... did you try what I proposed ?

[quote=jan_rifkinson]

Armando, Are you a forum moderator?Just askin'

[/quote]

I guess you could say that.

[/quote]

Is that official or did you just assume the mantle? Just askin'

[quote=jan_rifkinson]

Armando, Are you a forum moderator?Just askin'

[/quote]

Jan, what is happening here?

if you have a personal problem with someone, it would be great if you could sort it out by Personal Message. If you dont see it as personal, could you please be direct and say what the problem is without sniping. Otherwise it's going to be no goddamn fun being around here...

Many thanks, Tom

Tom, I'm not sure what you are referring to? Personal? Sniping? I simply asked a question. Having said that I do agree with the rest of your comment.

On a more substantive matter, I finally got the equation thing working.

Simply copying it over doesn't work for me as I like to try to understand what I'm doing.

Between your help, KeithB's list, Pierre's explanation, I finally started to understand -- more or less

I've always wanted to learn equations & that sort of thing & never have so this first little step was important to me.

Part of the problem to my understanding the process was realizing there were some "magic" words, i.e. built in functions & I had to adhere to them.

After that it was really a matter of logic...... and, in my case, trial & error.

Thanks for your patience.

As Pierre said.

Directly copying from the steps should've worked.

You don't have to learn anything about function, but just remember this :

When you see an equation looking like SomeFunctionName ( parameter 1, parameter2, etc.)

you must NOT change the "SomeFunctionName" name.

Parameters can usually be replaced with your own corresponding field names, or values. Sometimes they can't though.

Trying to find out how the function works by looking at the VB code in the editor section can help too, depending on your interest in the matter...

[quote=Armando]

Trying to find out how the function works by looking at the VB code in the editor section can help too, depending on your interest in the matter...

[/quote]

Also that's why I put -- link to nonexistent node ID 2122 -- in the manual, so a user could easily look through the default functions one at a time, to aid learning

[quote=KeithB]

[quote=Armando]

Trying to find out how the function works by looking at the VB code in the editor section can help too, depending on your interest in the matter...

[/quote]

Also that's why I put -- link to nonexistent node ID 2122 -- in the manual, so a user could easily look through the default functions one at a time, to aid learning

[/quote]

Very helpful, KeithB. Thank you.

Is there also a list of formats (don't know the right word but like the following)

<format>currency</format>

Do have a look at Field Management Dialog - Equations

I tried to distill Armando's answers to my questions re this type of equation, using an example from the sample file:

Duration field is a number field.

It's row equation reads:

WorkTime(StartDate, EndDate, 8) - ( NonBillable )

to figure out a period of time you need to use the WorkTime(X, Y) equation, where X & Y are date fields (X being the beginning of the time period)