I want to track trends in usage over the last 12 months. I have an expression which gives me the total activity for each month, with company as the dimension. What I want to find out is how much is this month's usage over/under the 12 month average, but if the company didn't join us until, say, six months ago, I want the average to be six months, in other words: % change = (this month's usage)/(sum(current month to first month of usage)/(# of usage months))
So, if a company joined a year ago, it would be (this month's usage)/(sum(past year)/12). If they joined in January (i.e. 5 months ago), it would be (this month's usage)/(sum(January to May)/5). But I don't have a 'join date', so all I can do is guess if ALL the months previous to, say, January, are zero, then the customer joined in January.
Now, I know I can brute force this with a bunch of nested if's, e.g.YTDUsage= if(usage(Month-12)>0,if(usage(Month-11)>0,if(usage(month-10)..etc..,sum(Month-10 to This Month),sum(month -11 to This Month),sum(month-12 to this month))))))) (many closing parentheses required but..
that look inelegant and a pain to maintain. Is there an elegant way of accomplishing the same thing?
