Distributing month amount on days

Thu, 19 Sep 2013 07:09:58 GMT

Hi!

I have a budget file (excel) that has a budget amount for each month in a year. What I want to do is to distribute this amount to every day of the month. I guess I need to create some sort of loop do distribute the amount.

First I need to calculate the number of days in each month to know how many times i need to divide my month amount, which really isn't that hard.

But how do I create the loop to distribute the amount? Do I need 2 loops? One for every month and one for each day of the month?

Appreciate any help or suggestions!

Re: Distributing month amount on days

Thu, 19 Sep 2013 11:52:00 GMT

Hi Kim,

You can try the below and adjust it based on your data model:

let vmindate = num(MakeDate(2012,12,1));
let vmaxdate = num(MakeDate(2013,4,30));

TableDates:
LOAD $(vmindate)+RowNo()-1 as PeriodDate,
Day(MonthEnd($(vmindate)+RowNo()-1)) as NumberOfDays,
Year($(vmindate)+RowNo()-1) as Year,
num(Month($(vmindate)+RowNo()-1)) as Month
AutoGenerate $(vmaxdate)-$(vmindate)+1;

Inner Join(TableDates)
LOAD * Inline [
Year, Month, Budget
2012, 12, 14000
2013, 1, 10000
2013, 2, 11000
2013, 3, 9500
2013, 4, 15000
];

DailyBudget:
LOAD *,
Budget/NumberOfDays as DailyBudget
Resident TableDates;
DROP Table TableDates;

Hope this helps.

topic Re: Distributing month amount on days in QlikView

Distributing month amount on days

Re: Distributing month amount on days