topic Expression Sum Year Start to Month Year in QlikView

Expression Sum Year Start to Month Year

Tue, 06 Mar 2012 05:37:58 GMT

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.

Expression Sum Year Start to Month Year

CELAMBARASAN — Tue, 06 Mar 2012 05:56:09 GMT

Hi,

Check with this expression.

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

Celambarasan

Re: Expression Sum Year Start to Month Year

jagan — Tue, 06 Mar 2012 05:56:18 GMT

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.

Expression Sum Year Start to Month Year

perumal_41 — Tue, 06 Mar 2012 05:56:42 GMT

Try this Expressiion .i thing Useful for u

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

Regards

Perumal

Expression Sum Year Start to Month Year

Tue, 06 Mar 2012 06:10:45 GMT

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

Expression Sum Year Start to Month Year

perumal_41 — Tue, 06 Mar 2012 06:16:08 GMT

Try this Expressiion .i thing Useful for u

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

Expression Sum Year Start to Month Year

CELAMBARASAN — Tue, 06 Mar 2012 06:29:33 GMT

Hi,

Try this expression in that case

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

Celambarasan