No of Days in Month

[upaliwije]

Hi Friends

I have following sample data in my data table.

MONTH  BUDGET

JAN     3100

FEB     2800

MAR     6200

APR     6000

MAY    3100

JUN     9000

JUL     9300

AUG    6200

SEP    6000

OCT    3100

NOV    9000

DEC    9300

Above Budget figures are monthly figures and I want to calculate daily and weekly budget based on no of days for a month and week. Kindly help me to write the script for data load with split budget.

[tresesco]

If you want to get no of days in a month, you could try:

=Day(MonthEnd(Date))

For data like you have shown, you could try something like:

Load

          MONTH,

          BUDGET,

          BUDGET/Day(MonthEnd(Date#(MONTH, 'MMM'))) as DailyBudget

From <>;

[upaliwije]

Thanks Tresesco,

That works fine. The other part of post , How can I get number  of days for weeks? Pls help

[tresesco]

Days in weeks doesn't vary. Does it? Shouldn't that be 7 always?

[upaliwije]

When you take the month of Oct this  year  it only 3 days for the last week . That is 29,30,31.  I want to counts the days likewise

[tresesco · Accepted solution]

Create a Master Calendar in the script and then a Week field like:

Load

          Date,

          Ceil(Day(MonthEnd(Date))/7) as MonthWeek

          Year(Date) as Year,

          Month(Date) as Month

Now at the UI, if you select Year 2018 and Month as Oct and Week 5

Text box expression: =Count( Distinct Date)    will give you 3

Hope this gives you an idea - how to approach.

[upaliwije]

Great,

Thanks