Budget per day to be spread across days in month to get weekly budget

[Bird_za]

Hi all, I could really use some help with the following...

I have a budget calendar set up as seen below where "Budget Sales" is an amount set for the month only shown on the first of each month. I have used functions to derive the number of days in the months that I want to spread the "Budget Sales" amounts into. I then managed to get the values for the "[Budget per Day]", these are different for each month because the Budget assigned to each month is different.

 

results seen below 

The outcome I seek is to now spread these "[Budget per Day]" values into the days in each month so that I can derive a weekly budget. If it is possible on the front end that would be great, I would like to change my data load editor as little as possible.

Thanks 

-Bird

 

[justISO]

Hi, first, in load script you can calculate everything you need in your first load, without residents

LOAD
  [Budget Store Code],
  date([Budget Date]) as Date,
  MonthEnd([Budget Date]) as MonthEnd,
  MonthStart([Budget Date]) as MonthStart,
  [Budget Sales],
  day(MonthEnd([Budget Date])) as [Days in Month],
  [Budget Sales]/day(MonthEnd([Budget Date])) as [Budget per Day]
...

In front end you can populate your sales per day with aggregation

aggr(nodistinct sum([Budget Sales]), MonthEnd) / day(MonthEnd) //or / [Days in Month]