Group by sum not working

[#1]

Hi this is my first post, I'm reasonably new to Qlikview and am having trouble with a sum.

I am trying to calculate the number of appointments that have been created prior to the month starting.

When I try and group by a month (or date) I get an unspecified script error. When I group by another field (appointment key)  I get no error.  I don't understand how to get arround this problem.

ie

Appointment Created Date        Appointment Date

1/3/2012                                  17/4/2013  --> Count this appointment    

2/4/2013                                 14/4/2013 --> do not count this appointment  

FactTableTmp:

load
          AppointmentKey,
            [Appointment Created Date],
            AppointmentDate,
          Num(MonthStart(AppointmentDate)) as TempDate,
   Resident FactTableTmpA;
Left Join (FactTableTmp)
LOAD
          AppointmentKey,
          sum(if(MonthStart([Appointment Created Date]) < MonthStart(AppointmentDate),1,0)) as [Sum Prior Month Appt]
Resident FactTableTmp
Group By TempDate;

[#2 · Accepted solution]

I solved the problem using some set analysis:

//Calculate the number of appointments for the month that were created in previous months

sum(aggr(sum( {$<

          CalendarYear={'=$(#=max(CalendarYear))'}

          ,CalendarMonthNo={'=$(#=max(CalendarMonth))'}

          ,CalendarWeekStart = {'<=$(=max(CalendarWeekStart))'}

          ,CalendarDateNo = {'<=$(vReloadDate)'}

          ,[Reason Group] = {'HC'}

          ,[Appointment Created Date] = {'<$(vMonthStart)'}

          >}AppointmentCounter ),CalendarMthYr))

+

//Calculate the number of appointments that are created during the current month.

sum( {$<

          CalendarYear={'=$(#=max(CalendarYear))'}

          ,CalendarMonthNo={'=$(#=max(CalendarMonth))'}

          ,CalendarWeekStart = {'<=$(=max(CalendarWeekStart))'}

          ,CalendarDateNo = {'<=$(vReloadDate)'}

          ,[Reason Group] = {'HC'}

          ,[Appointment Created Date] = {'>=$(vMonthStart)'}

          >}AppointmentCounter )