I’m trying to annualize some data and I have this expression:
= 12*(Sum( {$1<Year = {2011}>} Volume) / (vArchiveMonth))
I’m summing up all of the volume up to “today” and dividing it by the current archive month (kind of like an accounting “book” date) and then multiplying by twelve. The variable, vArchiveMonth is set at load time but can change at runtime.
But sometimes people add data forward – into the next month beyond the archive month. So I want to limit the numbers I sum() to only include data up to the archive month. So I tried this:
= 12*(Sum( {$1<Year = {2011}, NMonth <= {$(vArchiveMonth)}>} Volume) / (vArchiveMonth))
(NMonth is a numeric month field and I want it to be less-than-or-equal-to the archive month).But that's no good because I need a "Set" for Month to equal. -- I guess. So I tried this:
= 12*(Sum( {$1<Year = {2011}, NMonth = {$(vMos)}>} Volume) / (vArchiveMonth))
where vMos is a variable = 1,2,3,4,5,6,7 (just going up to July right now.)
And it works! Hooray! But it sucks! Boo! Way too much hard coded stuff is going on there.
I'm sure I can figure out how to deal with the Year but how do I limit the number of months?
One way is to be able to set and change the value of vMos at runtime. And it should be relative to vArchiveMonth. something like this:
for (int i = 1; i <= vArchiveMonth; i++) vMos += i; // needs to be calulated at runtime because vArchiveMonth can change during.
Or, I could try something completely different if anyone would like to suggest it.
thanks. -Erik.
