5 Replies Latest reply: Oct 13, 2017 9:06 AM by Stefan Wühl RSS

    Sum of a 'lookup' list when a filter on another table excludes it

    Daniel Young



      Lets say I have two tables, "Births" and "Population"


      The Births table contains details of births, together with the age of the mother, grouped into 5 year segments i.e. 15-19,20-24,25-29 in the field FIVE_YEAR_AGE_BAND.  There is also the gender of the baby - Male,Female in the field BABY_GENDER

      This links across to another table on FIVE_YEAR_AGE_BAND and MOTHER_GENDER in order to get the population list size for that age band/gender.


      I want to calculate the birth rate per 1000 population, so (sum of births / sum of population) * 1000


      Everything is good, but if a user wishes to select 3 age bands, like the 15-19,20-24 and 25-29 as above, and a BABY_GENDER of Male, the list size is only the sum of list sizes which have a birth against them - the population for the excluded isn't counted.

      In the example I have selected everything as above and the list size 'technically' 113 as per the data model, but I am after 141, including the list size of 28 for the age range 20-24 which is excluded as there was no birth in that group with a BABY_GENDER of Male.  Is there a way to include it?  This is a relatively simple example, in reality, there are lots of other filters which could narrow the activity, but crucially, if the user has selected any FIVE_YEAR_AGE_BAND, I want the sum of the selected populations (in the chart) - but it is made a bit more complex because in my real development there are other dimensions which I still want honouring - by that I can't just use {1<FIVE_YEAR_AGE_BAND=<enter variable that concatenate all selected age ranges>} because the 1 would include a raft of other age sizes for other doctors surgeries.


      I've messed with +P()+E() and other crazy set analysis but can't seem to crack it.


      Example attached.

      Many thanks, Dan.