17 Replies Latest reply: Jan 24, 2014 9:34 AM by Daniel Roberts RSS

    Set analysis provide average based on one specific field selection

      Hi,

       

      I have a field called 'IllnessGroup'. One illness group may contain many patients all of whom may have had many events. I have a map with each of the patients plotted. What I am wanting to achieve is to have all the patients with an average waiting time (accross all the events they have) of less than the population average to be green else be red. To start with I used the following expression within the 'Background colour' of the chat expression on the map:

       

      IF(AVG(TimeBetweenEvents)>[AVG(TimeBetweenEvents)],red(),green())

       

      In the script I have calculated the field [AVG(TimeBetweenEvents)]. This roughly works as it does indeed colour the dots red or green depending on the average value of 'TimeBetweenEvents' for each patient. The problem is that some illness groups have much longer 'TimeBetweenEvents' values meaning that if you select that 'IllnessGroup' you will always see a red dot.

       

      Instead what I really want is for the colour coding to be relative to the users selection on 'IllnessGroup'. For example if the user has the 'IllnessGroup' selection of Heart attack and stroke and the user has select just three specific PatientIDs I would like the map to display the colours for those three users as green or red based on if they are above or below the collective average 'TimeBetweenEvent' for the all Patients within the 'IllnessGroup' of Heart attck and Stroke.

       

      Any pointers would be greatly apprecaited.

       

      Thanks

       

      Dan