Set Analysis:Month Year sum

[#1]

Hi,

I have three columns in my Excel source. (Year,Month,Payments)

I need to calculate sum of Payments on the basis of Year and Month column e.g.for Year=2008 and Month=Mar, Sum(Payment).

Thanks,

Sunil

[#2]

I am writing below code in set analysis:(This takes 2008 May data)

Sum

({ $<Year=, Month=> < Year = {"=$(=(Year(Today())-1)) =$(month(addmonths(today(),-1))"} >}Payments)

However,it is not working. I need 2008 May sum of Payments.

[#3]

Hi Sunil,

Can I propose an easier solution:

Create a Calculated Dimension (eiter calculated dimension or in the script) a field combining the year and month. (year * 100) + month = (2008 * 100) + 3 = 200803. Then create a simple expression sum(Payment).

Dimension Expression

200803 sum(Payment)

This should do the job.

Regards,

Werner

[#4]

Problem is that month and year will keep changing. e.g. in this month if it is 200803, next month report run will have 200804,etc.

I need a set exp or if exp like i have mentioned below to suffice my requirements:

Sum

(

if

(

Year

=(Year(Today())-1

)

OR

Month

=Month(AddMonths(Today(),-1)),open_accts,0

))

[#5]

Try this:

SUM(IF(YEAR=year(today())-1 and MONTH=month(today())-1,Amount))

[#6]

It does not work. I am getting value as '0'.

[#7]

Please post the QVW file so I can have a look. Maybe I missunderstood.

[#8]

Problem is resolved.Actually in Excel data, Month is shown as "Jan,May" and expression Month(Today()) returns '5' so unable to match it. Now I ahve converted 5 as 'May' so it worked.

Thanks for all the help.