Expression Sum Year Start to Month Year

[#1]

Hi,

I have problem in sum value of data from year start to selected Month Year. For example, i have script like this:

Test:

LOAD * INLINE [

    ID, Date, Amount

    1, 01/01/2008, 1000

    2, 01/01/2009, 2000

    3, 01/01/2010, 3000

    4, 01/01/2011, 1200

    5, 02/01/2011, 3500

    6, 03/04/2011, 1250

    7, 05/05/2011, 4550

];

Calender:

load

Date(Date) as Date,

Day(Date) as Day,

Year(Date) as Year,

Month(Date) as Month

resident Test

order by Date asc

What i want is when i select :

- Jan 2010 the result should be 6000

- Jan 2011 the result should be 7200

- Apr 2011 the result should be 11950

anyone know the expression? thanks a lot.

[CELAMBARASAN · Accepted solution]

Hi,

     Check with this expression.

     Sum({<Date={"<=$(=Max(Date))"},Month=,Year=,Day=>} Amount)

Celambarasan

[jagan]

Hi,

Try using the below expression

for the current year start to selected month

=Sum({<Date{'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} Amount)

If you Month/Wee/Day filters in your sheet, then use the below expression

=Sum({<Month=, Week=, Day=,Date{'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} Amount)

If you want amount for the years before and the current Year then use this

=Sum({<Month=, Week=, Day=,Date{'<=$(=Max(Date))'}>} Amount)

Hope this help you.

Regards,

Jagan.

[perumal_41]

Hi

Try this Expressiion .i thing Useful for u

Sum({<MonthFiled=,Date={'>=$(=Yearstart(max(Date)))<=$(=max(Date))'}>}Amount)

Regards

Perumal

[#5]

thanks all for your answer.

and one question again, what is the expression if i have case like i  mentioned above, but the month is month-1.

so:

if i select may 2011 the result should be 11950 (sum year start to selected year month-1.

any idea?

thanks all

[perumal_41]

Hi

Try this Expressiion .i thing Useful for u

Sum({<MonthFiled=,Date={'>=$(=Yearstart(max(Date))))<=$(=addmonths(max(Date),-11)))'}>}Amount)

[CELAMBARASAN]

Hi,

     Try this expression in that case

     Sum({<Date={"<=$(=MonthEnd(Max(Date),-1))"},Month=,Year=,Day=>} Amount)

Celambarasan