Dividing budget over weeks

[#1]

I have a table with a budget per product for the whole year, another table with the % of budget for a particular week. The percentage table is read using a crosstable, no issues sofar

To put the 2 together, I want to end up with per product 52 weeks with the divided budget.

What is the way to solve this ?

[brenner_martina]

Hi,

do you have a datefiled in budgettable? If yes, enter this function:

Week(Datefield) As Week

The name of the new field must be the same as the name of your Percentagetable.

If you want to use this tables over years, you must use the function

Weekname(...) because now the field will look like this: 2011/01

[suniljain]

you will get weekly budget by following formula.

=total_budget /((week( Weekend(Date))- week( Weekstart(Date)))

[johnw]

OK, so you have this?

YearlyProductBudgets:
year,product,budget

WeeklyProductBudgets:
year,week,product,percent

If you just want to do the calculation in a chart with year, week and product as dimensions:

budget * percent

For other combinations of dimensions, you may need to aggregate manually.

aggr(budget * percent,year,week,product)

But I suppose it'll be better to just allocate the budget in the script.

LEFT JOIN (WeeklyProductBudgets)
LOAD *
RESIDENT YearlyProductBudgets
;
LEFT JOIN (WeeklyProductBudgets)
LOAD *
,budget * percent as weekbudget
RESIDENT WeeklyProductBudgets
;
DROP TABLE YearlyProductBudgets;
DROP FIELDS
budget
,percent
;
RENAME FIELD weekbudget TO budget;