topic Re: Calculate Daily Budget From Monthly Totals in QlikView

Calculate Daily Budget From Monthly Totals

Thu, 21 Jul 2011 01:58:33 GMT

I have the following table loaded in my script:

LOAD
SourceID,
BudgetID,
FiscalYearID,
AccountID,
PeriodEndDateTime,
AccountClass,
AccountClassType,
AccountControlAccount,
AccountControlCode,
AccountCorporation,
AccountCorporationName,
AccountDescription,
Budget,
BudgetYTD,
RowUpdateDateTime
FROM
datasources\GlBudgetsByPeriod2011.qvd
(qvd);

I have actual amounts (not shown here) by day, and need to display daily budget amounts as well. The problem is that my budget amounts are monthly totals. How would you go about this? In the script? In the expression?

Calculate Daily Budget From Monthly Totals

Thu, 21 Jul 2011 02:03:29 GMT

I forgot to mention the obvious...What I need to do is divide the monthly total by the number of days in the month to arrive at a daily budget number.

I'm wondering if I would need to create a new table with the daily numbers (for several thousand accounts), or if I could reference the daily amount for each month in the expression.

Calculate Daily Budget From Monthly Totals

chriscammers — Thu, 21 Jul 2011 02:59:16 GMT

This may or may not be helpful but I wrote a blog about this a while back, it helps you deal with holidays and weekends when projecting your budget from monthly to daily.

http://biventure.blogspot.com/2010/03/handling-holidays-on-corporate-calendar.html

Re: Calculate Daily Budget From Monthly Totals

jarebpat — Wed, 09 Jan 2013 14:26:53 GMT

This seems to be a good solution. What would be the expression to show the monthly total all dated Jan 1st to be spread about to each work day? Note my budget table has the 1st of each month with the budget totals...I need to divide the sum of those lines dated Jan 1st and times it by max current workdays of this month to get each days totals? Do you have this part of the expression?

Thanks!

Re: Calculate Daily Budget From Monthly Totals

Fri, 09 May 2014 10:01:09 GMT

If any body got the complete solution of question asked by Jareb2012

for calculating daily budget from monthly total kindly reply.

Re: Calculate Daily Budget From Monthly Totals

miikkaqlick — Fri, 09 May 2014 10:26:15 GMT

Hi!

Assuming that all days have equal budget:

Calender:

Load

Period,

Date

...

Calender_div:

Load

Period

Count(Date) as Divider

Resident tmp_Calender

Group By Period;

Budget:

Load

Budget / Lookup('Divider','Period', Period,'Calender_div') as Daily_Budget

Resident tmp_Budget;

Join (Calender)

Load * Resident Calender)

Drop tables tmp_Budget, Calender, Calender_div;

If you want to add holidays or your company has some special dates, then you can give excel-file to your business users and they can define working days or production dates there and you can subtract those in script:

Open:

Mapping LOAD Date(A,'YYYY-MM-DD') as Date,

C as Open

FROM ...

Also with excel-file you can give different weight for days, if for example Monday has 50% of sales budgeted for certain week.

Br,

Miikka

Climber Finland

Re: Calculate Daily Budget From Monthly Totals

Fri, 09 May 2014 10:47:29 GMT

can u please tell what period is?

Re: Calculate Daily Budget From Monthly Totals

miikkaqlick — Fri, 09 May 2014 10:51:54 GMT

Period is year-month.

In above script something like this (depending on calender).

Year(PeriodEndDateTime)&Num(Month(PeriodEndDateTime),'00) as Period

Format really doesn't matter, it just has to be same in calender and in budget. Now it can be 2014-05, 201405, 1405, ...

Miikka

Climber Finland