Is there an elegant formula for this?

Mon, 13 May 2013 20:03:36 GMT

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?

Thanks,

Kevin

Re: Is there an elegant formula for this?

Mon, 13 May 2013 20:06:21 GMT

sorry, the nested ifs would look like:

YTDUsage = if(usage(month-12)>0,sum(usage(month-12 to this month),if(usage(month-11)>0,sum(usage(month-11 to this month),etc, etc.

topic Is there an elegant formula for this? in QlikView

Is there an elegant formula for this?

Re: Is there an elegant formula for this?