Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Check with this expression.
Sum({<Date={"<=$(=Max(Date))"},Month=,Year=,Day=>} Amount)
Celambarasan
Hi,
Check with this expression.
Sum({<Date={"<=$(=Max(Date))"},Month=,Year=,Day=>} Amount)
Celambarasan
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.
Hi
Try this Expressiion .i thing Useful for u
Sum({<MonthFiled=,Date={'>=$(=Yearstart(max(Date)))<=$(=max(Date))'}>}Amount)
Regards
Perumal
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
Hi
Try this Expressiion .i thing Useful for u
Sum({<MonthFiled=,Date={'>=$(=Yearstart(max(Date))))<=$(=addmonths(max(Date),-11)))'}>}Amount)
Hi,
Try this expression in that case
Sum({<Date={"<=$(=MonthEnd(Max(Date),-1))"},Month=,Year=,Day=>} Amount)
Celambarasan