divide month amount by days in month

[#1]

Hi All,

I have amounts linked to the last day of each month, that I need to convert to a daily amount i.e. amount / days in month.

I've created a calendar so have all the days for each month and have the amount currently showing on the last day of each month, but how in an expression can I say the amount for last day divided by number of days in that month?

Many thanks,
Phil

[Anonymous]

Hi Phil,

I Change little bit your code. Hope this give you the result you want.

Normal 0 21 false false false FI X-NONE X-NONE MicrosoftInternetExplorer4

data:

LOAD * INLINE [
Day, Amount
01/01/2010,0
02/01/2010,0
……
];

EndDays:
LOAD
 Date(Monthend(Day),'DD/MM/YYYY') as MonthEnd,
 Amount as EndAmount
Resident data where Amount>0 and not Isnull(Amount);

AllDays:
LOAD
 Date(Monthend(Day),'DD/MM/YYYY') as MonthEnd,
 Month(Day) & Year(Day) As MthYr,
 Date(Day,'DD/MM/YYYY') as Day
RESIDENT data;


Daily_Amount:
JOIN
LOAD MonthEnd,
 Day(MonthEnd) as NoDays,
 // EndAmount ,
 Num(EndAmount/Day(MonthEnd)) as [Daily Amount]
RESIDENT EndDays;

DROP TABLE data;

Anni

[#3]

Hi Anni,

Many thanks for that, calculating the monthly amount in the load is a much better solution for me.

Kind Regards,
Phil

[#4]

I saw this and did try to do exactly the same but found these solutions a bit to advanced.

I just created a variable:

LET MaxDaysInMonth =Day(monthend (Today()));

And this variable will always show the number of days in the month you are in right now.

[v_iyyappan]

Hi,

     Use the script like this

     MasterCalendar :

     Num(OrderDate) AS OrderDay,

In FirstChart

     Propertities ---> Dimension---> Secor, OrderDate,

    Enable the option of Suppress when value is null you will get the remove the 'Zero' value from chart.

Expression like this

=Sum(Amount) / Count(Distinct OrderDay)

Regards,

Iyyappan