I’m trying to build variables in an existing report to provide aggregate Max, Min, Average and Median cost calculations against various service types. The Max and Min values are working fine; but the Average and Median are sometimes working and sometimes throwing #ERROR.
What it seems to boil down to is a straightforward count of Provision IDs is also throwing #ERROR, and I think it’s because some of the provisions have multiple costs (from working with the data in Excel to create pivots). I can continue working manually, but I’d really like this to be automated as we are now going to need to provide the data monthly. I suspect it’s going to be a matter of inserting variables and ForEach/ForAll in the provision counts until they work, and use the same pattern in the Average and Median variable.
The data looks like:
Person ID, various bits of demographics
Each person can have one or more provisions of service type A, B etc with a start date (and sometimes an end date) with a unique ID that is open during or at the end of the month I’m calculating (I have flags for this). Each provision should have a single cost - but not invariably. I am aware there are some provisions with multiple costs in the data.
The aggregate table is split by term of service, setting of service and service type. The table is then filterable by a single piece of demographic information. I will probably also filter it on service type and term as well on the grounds we’re only ever asked for specific service types, and the rest of the service types are irrelevant.
My question is how I should be structuring the context operators; I always have trouble with these and usually waste a lot of time building them by trial and error. This is fine for a relatively simple formula, but complex aggregate formulae give me a headache.