topic Re: Calculating client growth per month in Integration, Extension & APIs

Calculating client growth per month

maxim1500 — Wed, 04 May 2016 13:53:30 GMT

Hi,

I am trying to calculate the client growth rate per month. I was able to get the period growth rate (for example, trailing 12 months):

(Count({<$(=vMaxDate)>} distinct [Client ID]) - Count({<$(=vPeriodLowerBand)>} distinct [Client ID]))/Count({<$(=vPeriodLowerBand)>} distinct [Client ID])

with vMaxDate = current date, vPeriodLowerBand = current date - 12 months

But I need to create a bar chart with monthly rates. The x axis would be months for the past year, and the y axis the monthly growth rate.

In other words, I would need to "aggregate" the rates by month instead of calculating for the whole period.

Thanks!

Re: Calculating client growth per month

maxim1500 — Wed, 04 May 2016 14:04:14 GMT

A little precision: the dataset contains monthly data. Don't need to group the dates by months...

Re: Calculating client growth per month

sunny_talwar — Wed, 04 May 2016 16:07:07 GMT

May be this:

(Count({<$(=vMaxDate)>} distinct [Client ID]) - Count(TOTAL <MonthField>{<$(=vPeriodLowerBand)>} distinct [Client ID]))/Count(TOTAL <MonthField>{<$(=vPeriodLowerBand)>} distinct [Client ID])

Re: Calculating client growth per month

maxim1500 — Wed, 04 May 2016 17:12:31 GMT

Hi Sunny,

Thanks for your help! I tried your suggestion, and it doesn't appear to solve my issue. Here is what I get

(Count({<$(=vMaxDate)>} distinct [Client ID]) - Count( TOTAL <[Snapshot Date]> {<$(=vPeriodLowerBand)>} distinct [Client ID]))

/ Count( TOTAL <[Snapshot Date]> {<$(=vPeriodLowerBand)>} distinct [Client ID])

Re: Calculating client growth per month

sunny_talwar — Wed, 04 May 2016 17:22:47 GMT

How about this?

(Count({<$(=vMaxDate)>} distinct [Client ID]) - Count( TOTAL {<$(=vPeriodLowerBand)>} distinct [Client ID]))

/ Count( TOTAL {<$(=vPeriodLowerBand)>} distinct [Client ID])

Re: Calculating client growth per month

maxim1500 — Wed, 04 May 2016 17:25:00 GMT

Now I only get a single month

Re: Calculating client growth per month

sunny_talwar — Wed, 04 May 2016 17:34:49 GMT

What all dimensions do you have? I think you need this month divided by last 12 months? You would need to use Above()/Below() functions to do this. Would you be able to provide a sample to play around with?

Re: Calculating client growth per month

maxim1500 — Wed, 04 May 2016 17:44:28 GMT

I didn't know I could use above/below in expressions. That's exactly what I was looking for! Thanks!

Re: Calculating client growth per month

sunny_talwar — Wed, 04 May 2016 17:52:58 GMT

That is awesome. I am glad I was able to point you in the right direction