Group by sum not working

Tue, 02 Apr 2013 22:51:47 GMT

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.

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;

Re: Group by sum not working

Wed, 03 Apr 2013 01:39:58 GMT

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( {$<