topic Set Analysis:Month Year sum in QlikView

Set Analysis:Month Year sum

Fri, 12 Jun 2009 16:05:28 GMT

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

Set Analysis:Month Year sum

Fri, 12 Jun 2009 16:40:40 GMT

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.

Set Analysis:Month Year sum

Fri, 12 Jun 2009 17:47:15 GMT

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

Set Analysis:Month Year sum

Fri, 12 Jun 2009 17:54:06 GMT

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

(

Year

=(Year(Today())-1

)

Month

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

))

Set Analysis:Month Year sum

Fri, 12 Jun 2009 18:22:05 GMT

Try this:

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

Set Analysis:Month Year sum

Fri, 12 Jun 2009 18:34:00 GMT

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

Set Analysis:Month Year sum

Fri, 12 Jun 2009 18:35:38 GMT

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

Set Analysis:Month Year sum

Fri, 12 Jun 2009 18:50:50 GMT

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.