topic AW:Dividing budget over weeks in QlikView

Dividing budget over weeks

Thu, 17 Mar 2011 17:51:04 GMT

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 ?

AW:Dividing budget over weeks

brenner_martina — Thu, 17 Mar 2011 18:05:15 GMT

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

Dividing budget over weeks

suniljain — Thu, 17 Mar 2011 18:14:06 GMT

you will get weekly budget by following formula.

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

Dividing budget over weeks

johnw — Thu, 17 Mar 2011 20:11:20 GMT

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;