If clauses; subsets and Intersections

Phillipa · March 12, 2012 7:18AM

Hi,

I have written a formula to calculate weightings for institutions in specific regions. It involves 12 nested if functions as we have 4 regions and 3 institution types.

Each record is weighted by a fixed number (dependent on institution and region) which is then divided by the number of records for that institution type in that region. Please see formula below.

I now need to append more time periods but my recordcount is counting all records for all periods. How can I limit the recordcount to the same time period as the record it is calculating without the formula becoming a monster.

Effectively I want to repeat the formula below for each timeperiod and could do it in the source files, however, I would prefer to find a solution in Omniscope

Any ideas much appreciated!

Thanks
Phillipa

IF(AND([Region]="North",[Institute]="University"), 9/RECORDCOUNT(SUBSET2([Region], [Institute],"North", "University")),
IF(AND([Region]="North",[Institute]="Non-university"), 172/RECORDCOUNT(SUBSET2([Region], [Institute],"North", "Non-university")),
IF(AND([Region]="North",[Institute]="Practice"), 92/RECORDCOUNT(SUBSET2([Region], [Institute],"North", "Practice")),
IF(AND([Region]="West",[Institute]="University"), 10/RECORDCOUNT(SUBSET2([Region], [Institute],"West", "University")),
IF(AND([Region]="West",[Institute]="Non-university"), 135/RECORDCOUNT(SUBSET2([Region], [Institute],"West", "Non-university")),
IF(AND([Region]="West",[Institute]="Practice"), 66/RECORDCOUNT(SUBSET2([Region], [Institute],"West", "Practice")),
IF(AND([Region]="East",[Institute]="University"), 9/RECORDCOUNT(SUBSET2([Region], [Institute],"East", "University")),
IF(AND([Region]="East",[Institute]="Non-university"), 118/RECORDCOUNT(SUBSET2([Region], [Institute],"East", "Non-university")),
IF(AND([Region]="East",[Institute]="Practice"), 68/RECORDCOUNT(SUBSET2([Region], [Institute],"East", "Practice")),
IF(AND([Region]="South",[Institute]="University"), 8/RECORDCOUNT(SUBSET2([Region], [Institute],"South", "University")),
IF(AND([Region]="South",[Institute]="Non-university"), 125/RECORDCOUNT(SUBSET2([Region], [Institute],"South", "Non-university")),
IF(AND([Region]="South",[Institute]="Practice"), 74/RECORDCOUNT(SUBSET2([Region], [Institute],"South", "Practice"))))))))))))))

daniel · March 12, 2012 11:44AM

If the integer values in your formula does not change due to the time period then you could break your formula down into more than one column.

Where you have the RECORDCOUNT(SUBSET2([Region], [Institute],"XXXXX", "XXXXXXXX"), you could make that as a separate column so just uses the function :

RECORDCOUNT(INTERSECTION(

SUBSET([Region]),

SUBSET([Institute]),

SUBSET([Time Period])))

Where the last row will account for the other time periods you may have. This will save you writing out everything single IF permutations into a single formula field.

Phillipa · March 13, 2012 4:40AM

Hi Daniel,

Thanks for that it's a good solution.

The only problem is that I am depivoting and sometimes pivoting data again. Won't this effect my count value. Or can I set the block to give static values so that when I add other operations in the data manager the count remains the same?

Phillipa

daniel · March 15, 2012 4:03AM

Those formulas above will break down if you pivot and de-pivot them. You will need to make them static to retain the values.

It is best in that case to do as much of this in data manager using a field organiser which will default to static values so you can pivot and depivot without the values changing. Just keep checking the preview after each block/operation to make sure it looks good.

Welcome!

Categories

Ideas Parade