Average of expression over dimension

[tchovanec]

In the table below the 13 wk calculation is below. I am trying to get the 145 and use it in the bar chart. I am trying to only color the prior week if the number is higher or lower than the 13 week average. What is happening is the color is not off the 145, but rather the number for that week. So in this case it is 11.5 for 4/17.

COUNT(DISTINCT

{<

FLG_NEW_PATIENT = {1},

FLG_FIRST_NEW_PT_APPT_BY_DEPT_SPECIALTY = {1},

DateType = {''APPOINTMENT_MADE_DATE''},

_W={">=$(=$(vAppointmentMadeDate13WeeksAgo)) <= $(=$(vMaxAppointmentMadeDateWeek))"},

MONTH = ,

YEAR = ,

DATE = ,

MONTHYEAR = ,

WEEKNUM= ,

WeekEnd=

>}

DATE_KEY & DEPT_ID)/13

;

[sunny_talwar · Accepted solution]

Try this:

COUNT(TOTAL DISTINCT

{<

FLG_NEW_PATIENT = {1},

FLG_FIRST_NEW_PT_APPT_BY_DEPT_SPECIALTY = {1},

DateType = {''APPOINTMENT_MADE_DATE''},

_W={">=$(=$(vAppointmentMadeDate13WeeksAgo)) <= $(=$(vMaxAppointmentMadeDateWeek))"},

MONTH = ,

YEAR = ,

DATE = ,

MONTHYEAR = ,

WEEKNUM= ,

WeekEnd=

>}

DATE_KEY & DEPT_ID)/13