I have a line chart which simply counts by period (distinct) values. I want to create another expression which is like backward accumulation but with user defined adjustments. I cannot get the expression correct and the attached example hopefully expresses what I am trying to achieve in more detail.
I don't know if I understand the question. But if I do, one solution is an AsOf table connecting each month to the correct current and previous months, and giving a denominator for that specific connection. Then aggregate by the connected months to get the final totals. See attached.
Set analysis can't solve it since a set is only calculated once for the chart, not once per dimension value.
Thanks for the reply. Unfortunately the counts cannot be pre-calculated - this is only an example but in reality there are many different fields that can be selected and so the counts are dynamic.
I don't know if I understand the question. But if I do, one solution is an AsOf table connecting each month to the correct current and previous months, and giving a denominator for that specific connection. Then aggregate by the connected months to get the final totals. See attached.
Set analysis can't solve it since a set is only calculated once for the chart, not once per dimension value.
My users wanted to dynamically change the 'denominator' values but I am trying to convince them its not possible outside a load. The only thing I can think is to have them stored in an xls they can maintain and then force a reload.
gordon.savage wrote:My users wanted to dynamically change the 'denominator' values but I am trying to convince them its not possible outside a load.
It's possible. I didn't quite pick up on that being what you meant in the original question. Attached is one solution. I've put the Denominator field in a separate table so that it isn't repeated unnecessarily, and turned it into an inputfield. Then I manipulate a chart to only show the denominators for WtyMth, since I don't want them changing the raw data or accumulation. Then I allow input of the data by using an inputsum(Denominator) for the expression. In the example, I only allow entry for the most recent 10 months, but this could be expanded to whatever you need. A 0 value appears to exclude the month instead of causing a division by 0, simplifying things.
There's probably some way to avoid the inputsum() and still allow input of the fields for just WtyMth. A table box avoids the inputsum(), but I don't think can exclude the other accumulation types. And just having Denominator as an expression didn't seem to allow input. I don't have a lot of experience with input fields, so I'm just guessing that what I have could be simplified.
