Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Expression Sum Year Start to Month Year

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.

1 Solution

Accepted Solutions
Highlighted

Hi,

     Check with this expression.

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

Celambarasan

View solution in original post

6 Replies
Highlighted

Hi,

     Check with this expression.

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

Celambarasan

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Partner
Partner

Hi

Try this Expressiion .i thing Useful for u

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

Regards

Perumal

Highlighted
Not applicable

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

Highlighted
Partner
Partner

Hi

Try this Expressiion .i thing Useful for u

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

Highlighted

Hi,

     Try this expression in that case

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

Celambarasan