InfoQube IM - Community

Submitted by Tom on 2014/11/12 08:41

There's various possibilities here but I'll just give one example:

You have a budget of say, 5000

The available budget decreases as it gets used up - as you spend money and/or time.

Any tips here ?

A 'Budget' field could be an equation that subtracts the 'cost' columns from 5000.

But that solution seems to only work for one project then.

How could I do this for multiple budgets simultaneously ?

is there another way?

TIA, Tom

Comments

I don't have much time to look into the many possibilities right now, but the first one that comes to mind is a column equation 5000-sum(children).

This works if there are no sub-sub-items however.

Else, a standard column equation sum(children) and a row equation should do the trick, If you want, you can also use conditional formats to highlight (background color change as 5000 approaches, equals, and exceeds)

HTH !

>How could I do this for multiple budgets simultaneously ?

As Pierre said, if the constant number in your column equation is 5000, then it's not a problem. Just create different items under which you'll create variations on the same budget.

You just have to realise that all numbers put in the column will add up add infinitum, unless you use a conditional equation.

So a simple column equation would look like :

A conditional equation would look like (sorry I left the caret in there) :

mySum() is just a variant of the normal equation that needs another field to function. The fictive needed field here is AddThis (it can be any Boolean Y/N field you create for that purpose; show it in the grid to make things easier)

So, for an item you create, if the field AddThis is checked, the amount in the field where the calculation takes place will be included in the calculation. If not, it won't. ***

Of course, you can add some complexity to this by also having a row equation to add taxes or not, divide the amounts if you're sharing costs between people. etc.**

Then, you can also use conditional formatting to Color amounts in the field cells that are included in the calculation (to make it easier to see what amounts are part of the equation) , or color the column cells which amounts reache 0, 100, 5000, or whatever...

**Column equations have priority over row equations, so that means that the total of the calculation of subitems will always supercede a row equation's total. (Why ? simply because no column equation calculation would ever occur when there's also a row equation that would always have priority...)

*** I have a personal variation on the mySum() function -- the idea was to nullify the result when there was nothing to add. Maybe it's not necessary anymore (haven't tested its cleverness recently), but here it is

(just copy/paste this at the end of your "user code" or "this database" code : tools->Visual basic editor : paste the snippet -> click on Validate code, and then click on save ):

Function mySum(param, x) ' Calculates the sum of the array x if param is true

Dim d, i, ok

ok=False

d=UBound(x)

For i=0 To d

If param(i)=True Then

mySum = mySum+NZ(x(i))

ok=True

End If

If ok=False Then mysum=Null

End Function

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

First, thanks very much for the in depth answers :thumbsup:

I'll have to go back to IQ school though ;-)

Re the amounts -

1) the budgets are unfortunately not always the same :-(

2) Pierre says:

> This works if there are no sub-sub-items however.

Does that mean it will not work at all if there are 'grandchildren' ? -- or that they will be ignored?

I presume the column equation would add *all* entries for that field - but the AddThis equation seems to be a good workaround.

Because of the varying budgets, I'm wondering would I be better with a standalone app (a donationcoder coding snack maybe) where I add my totals, say, at the end of each day, and the remaining amount gets displayed. (Or maybe a spreadsheet - I'm a basic user there but it seems it would be a good way of keeping an overview of multiple projects.)

I don't think you need another app for that. I manage multiple budgets myself for various projects.

Here's a concrete and simple example, following Pierre's suggestion (just did it in the sample Welcome grid as an example) :

I've created 4 fields : BudgetedAmount , Cost, AddThis, TotalBudgetMinusCost

1- BudgetedAmount : number field, nothing special

2- Cost : number field, contains a hierarchy/column equation : mySum(addthis

Check automatic recalc.

3- Addthis : Y/N or Boolean field, nothing special

4- TotalBudgetMinusCost : number field that contains a row equation :

= iif(isnull(BudgetedAmount) or BudgetedAmount=0,null,BudgetedAmount - Cost)

and some conditional formatting :

Format : BackColor=&h6EECFF

Criteria : <0

Note that the equation could be simpler : = BudgetedAmount - Cost

But the function I use here prevents "0" to appear in the column in certain circumstances. Probably shouldn't happen, but it does, sometimes.

it looks like (removing the obvious parts, like the number field selection, etc.)

So, as you see, you can have multiple budgets, with various budgeted amounts, etc.

Of course, this might not be what you want; it,s just a quick exa,ple.

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

And, as I forgot, here are multiple budgets, using some of the same (sub) items under different parents with different allocated values.

Note that there's currently a small bug where moving children under the parent won't automatically activate the calculation. I recommend unchecking/checking the "addthis" field under the parent to reactivate the calculation

Now if you also want to try various costs for the same items you could create several "cost" fields and several "TotalBudgetMinusCost" fields (e.g. TotalBudgetMinusCost1, TotalBudgetMinusCost2), and change the equations to have, say, cost1 correspond to a potential TotalBudgetMinusCost1 and set them all in a grid in a way that makes sense to you.

Of course, the more you add complexity, the more... complex it gets.

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

Many thanks Armando - I hope to get to try it out tomorrow -

instructions look nice and clear, but I suspect I will surely have some questions when trying to implement !

[edit] below I'm just thinking out loud/ planning how to do this [/edit]

I initially used IQ for time-tracking, but long-term I never really got a system successfully going involving:

Time (+ Costs) [etc. etc.] => Invoice Billable amount.

(This was probably as much to do with lack of discipline as anything else)

I'm now having to keep better track of what done - and the remaining budget.

Main factor is time directly related to individual drawings, for which I used make seperate entries under the relevant drawing (file-linked) item e.g.:

Topographical Map 13.5(hrs) [cost]

Drawing 4(hrs) [cost]

Research 2(hrs) [cost]

Drawing 4.5(hrs) [cost]

Paperwork 3(hrs) [cost]

I guess I would ctrl_drag(+) each relevant item into a Budget grid and make it sub-item of the related Budget item.

If I want to see what it's related to, I can show the ItemParent field

Okay, enough thinking out loud - will get back to you soon - thanks again for all the help.

Yes tell us how it goes.

BTW, the "cost" field in my example only as a column equation, but it could also have a row equation to get the right amount automatically from other fields like hrs and rate, or something like that.

the cost row equation would look like :

= hrs * rate

(Of course, since column equations have priority over row equations (otherwise no total of subitems would ever be produced!), the parents would always show the total of the costs in subs, and not their own row equation results..)

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

:-( I had a long reply which disappeared when I tried to upload a screenshot :-(

Dont know if the problem with Firefox or the site ...

I'll try and summarize:

the hierarchy is not working in the Cost field.

I resued the existing Cost field which already had the following:

# Row equation relating to duration & hourly rate (xx) - this is working ok

= ZN([duration] * xx)

# "Options" has:

<format>currency</format>

# I added

Parents = mysum(addthis [exactly as in your screenshot above, with recalc and 'No values' options ticked]

which didnt work - it looks unfinished so I tried:

mysum(addthis)

which didnt work either

NOTE:

it didnt work for a TLI with sub-items having value in the Cost field and the AddThis field ticked - same as in your screenshot above

Am I doing anything obviously wrong?

If not I can try a sample file and upload it

Hi Tom,

- Did you create the other fields ? The addThis field especially ? All the steps need to be followed precisely. Of course, you can change the fields names, but the equations/functions need to match those names.

- It's also possible that you don't have the function mySum() in your user code, for some reason.

Try to copy/paste this at the end of your "user code" or "this database" code :

tools->Visual basic editor :

1- paste the code at the end, without changing anything to what's already there.

2- click on Validate code,

3- and then click on save

code to copy and paste :

Function mySum(param, x) ' Calculates the sum of the array x if param is true

Dim d, i, ok

ok=False

d=UBound(x)

For i=0 To d

If param(i)=True Then

mySum = mySum+NZ(x(i))

ok=True

End If

If ok=False Then mysum=Null

End Function

If this fails, then yes, you could upload a file... but it shouldn't be complicated.

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

[quote=Armando]

Hi Tom,

- Did you create the .... addThis field especially ?[/quote]

yes Armando, sorry, you got the summary due to disappearing post, but yes, I followed all instructions.

The Cost hierarchy problem is only related to the AddThis column (I'm presuming). As noted:

[quote=Tom]

NOTE:

it didnt work for a TLI with sub-items having value in the Cost field and the AddThis field ticked - same as in your screenshot above[/quote]

I presume capitals dont make any difference? (AddThis field as opposed to addthis equation?)

I will try adding your code later, thanks!

Sorry Tom, I was in a hurry. But, as you say, capital don't make a differences. VBScript and IQ aren't case sensitive.

Good luck and see you later!

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

[quote=Armando]

Sorry Tom, I was in a hurry. But, as you say, capital don't make a differences. VBScript and IQ aren't case sensitive.

Good luck and see you later!

[/quote]

Thanks for your time Armando (and here I am (above) giving you a hard time! :-))

I added the code, (FWIW I searched the VB Editor contents and 'mysum' was not in there).

Went back to the grid - at this stage I just had one subitem - it had a Cost entry and had AddThis ticked.

I refreshed a couple of times and still no success with the Parent showing the value in the Cost field.

I then added another item, manually gave it a Cost value - I didnt tick AddThis before refreshing - but bingo!

- it now works as soon as I tick the AddThis field i.e. without refreshing even :thumbsup:

So I guess that was some type of startup hiccup.

Thank you very very much for your help Armando!

(via dc!)

Very glad to hear, Tom!

Don't panic if there are a couple bugs in column equations. I've detailed them somewhere and Pierre is solving them -- hopefuly! ;-)

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

This is great news. Thanks Armando for your help !

I'm being lazy here... why was the AddThis field (and MySum) required for you Tom ? You have sub-items with Cost values that you want to ignore ?

[quote=Pierre_Admin]

This is great news. Thanks Armando for your help !

I'm being lazy here... why was the AddThis field (and MySum) required for you Tom ? You have sub-items with Cost values that you want to ignore ?

[/quote]

Armando?

It was just a suggestion. What it does though is that if your calculations happen lower in a hierarchy they don't bounce up infinitely as only AddThis will be taken in into account.

It's also good to be able include/exclude something when you're working on a budget... I find it useful, maybe I'm the only one.

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

OK !

Appart from the added complexity, which can certainly be useful at times, I was afraid that users will forget to tick the AddThis field causing incorrect totals.

Best is try out both ways and see what works for you !

[quote=Pierre_Admin]

OK !

Appart from the added complexity, which can certainly be useful at times, I was afraid that users will forget to tick the AddThis field causing incorrect totals.

Best is try out both ways and see what works for you !

[/quote]

Agreed, of course. Tom (as I think I said earlier) you can just use sum and forget about the addThis field if it fits you better. I use Sum in certain cases too.

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

[quote=Armando][...] copy/paste this at the end of your "user code" or "this database" code :

tools->Visual basic editor :

1- paste the code at the end, without changing anything to what's already there.

2- click on Validate code,

3- and then click on save

code to copy and paste :

Function mySum(param, x) ' Calculates the sum of the array x if param is true

Dim d, i, ok

ok=False

d=UBound(x)

For i=0 To d

If param(i)=True Then

mySum = mySum+NZ(x(i))

ok=True

End If

If ok=False Then mysum=Null

End Function

[/quote]

Win. 7 x64 // IE9 // IQ Version 0.9.26Pre-Rel38 build 2015-01-26 23:21:48 (Running in Portable mode)

Attention:

this code (quoted above) has been deleted from my IQ VB-editor - I presume after one of the last two updates (I'm not sure when exactly it happened)

(I added it again and it's working as expected again)

[quote=Tom]

Attention:

this code (quoted above) has been deleted from my IQ VB-editor - I presume after one of the last two updates (I'm not sure when exactly it happened)

(I added it again and it's working as expected again)

[/quote]

Tom, where did you add the code?

1-"user code",

2- "this database" or

3- "system code"

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

hi Armando

I'm presuming you didnt lose anything then (that was why I highlighted it - in case)

It seems I added it to system code -

tbh I didnt even notice those options - I see I missed the "user code" or "this database" suggestion in your original instruction post above

Nothing to see here - move along now please :-)

(Thanks as ever Armando!)

Hi Tom,

"system code" is controlled by IQ/IQ's developper (Pierre).

"User code" and "this database" are yours.

If you want the code to be database specific then choose "this database", otherwise "User code" for code that can be used from all databases.

I wonder if Pierre could make the "system code" read only. It's good to be able to read it, but maybe not to change it since it'll probably be modified after an update.

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

[quote=Armando]

I wonder if Pierre could make the "system code" read only. It's good to be able to read it, but maybe not to change it since it'll probably be modified after an update.

[/quote]

Good suggestion, will do !

Hi Armando,

dont know if you remember this one -- calculates remaining budget.

'mysum' is used in the 'Cost' field:

Parents = mysum(addthis [Children]

AddThis is the y/n field to include/exclude entry

You recommend adding this code to make it work:

[quote=Armando]

code to copy and paste :

Function mySum(param, x) ' Calculates the sum of the array x if param is true

Dim d, i, ok

ok=False

d=UBound(x)

For i=0 To d

If param(i)=True Then

mySum = mySum+NZ(x(i))

ok=True

End If

If ok=False Then mysum=Null

End Function

[/quote]

I see 'mysum' is already in my new database (below), but is different to the above.

Also the the AddThis field is not working, so I thought best to check if below code will work same as above -- if not would adding yours cause a conflict of interests ? :-)

' The following functions can be used in Hierarchy calculations. Typically: parent = mysum (children)
'-------------------------------------------
Function mySum(IncludeIt, x) ' Calculates the sum of the array x if IncludeIt is true
dim d, i

d=ubound(x)
for i=0 to d
if IncludeIt(i)=true then mySum = mySum+NZ(x(i))
next

end Function

Hi Tom!

There won't be any conflict if you paste the function in your user code. (Don't paste it in the system code -- I don't think you can anyway as it's read only)

IQ reads functions in that order of priority :

- this databse

- user code

- system

Function mySum(param, x) ' Calculates the sum of the array x if param is true

Dim d, i, ok

ok=False

d=UBound(x)

For i=0 To d

If param(i)=True Then

mySum = mySum+NZ(x(i))

ok=True

End If

If ok=False Then mysum=Null

End Function

The difference in the function is that will insert a "null" value in the parent field if there's nothing to calculate in the children. Maybe did this to prevent some calculation issues at the time.

IQ geek

Windows 8.1

CPU: Intel i5 2.6ghz

thanks Armando, will try sometime tomorrow

[quote=Armando]

The difference in the function is that will insert a "null" value in the parent field if there's nothing to calculate in the children. Maybe did this to prevent some calculation issues at the time.

[/quote]

yeah, ticking 'AddThis' field to include the item doesnt work without it -- and I can live without that, but can be helpful, and I've gotten used to having it :-)

All the best

Tom

Forgot to say :

[quote=Tom]

Parents = mysum(addthis [exactly as in your screenshot above, with recalc and 'No values' options ticked]

which didnt work - it looks unfinished so I tried:

mysum(addthis)

which didnt work either

[/quote]

yes, even if it looks weird, its mysum(addthis

My guess is that you're just missing the function I mentioned. Once you'll add it it should work.

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

[quote=Armando][quote=Tom]Parents = mysum(addthis [exactly as in your screenshot above, with recalc and 'No values' options ticked]

which didnt work - it looks unfinished so I tried:

mysum(addthis)

which didnt work either

[/quote]yes, even if it looks weird, its mysum(addthis[/quote]

^ just seen this post:

I used mysum(addthis) above and it worked too -

but it may have been what caused the startup hiccups though ...

Armando suggestion will work (note that it is not required to have "automatic recalculation" checked, as there are no date fields involved)

My second suggestion should also work.

Column eq. parent=sum(children) for the Cost field (use a dedicated field if you don't want this equation to apply across your whole IQBase)
Row eq.: Balance = Allocation - Cost. Allocation field would contain 5000 in your example. Different budgeting groups would have different values. Balance field would be the one you're watching (conditional formats, separate grid and/or filters to bring to your attention groups that are in the "red", etc)

~~> (note that it is not required to have "automatic recalculation" checked, as there are no date fields involved)~~

~~Oups!~~

~~that was a mistake as I used my own fields to provide the screenshots...~~

Actually, in this case, it's needed. The date thing is for row equations, Pierre! For once, you got mixed up!

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz