5 Replies Latest reply on Feb 10, 2017 4:56 PM by Shinichiro Murakami

# Growth Rate for Running table of totals

Hi there,

I have a table which contains data for what we may call [Number of Customers]. Every day, the table adds a new row with the new *total* number of customers.

January 1, 2015: 1

January 2, 2015: 5

January 3, 2015: 8

..etc.

I'm looking to create a line chart which shows the number of new customers for each month, with a tooltip / label displaying the growth rate for that month. Eventually I will want to do this for years, but I figure if I can understand how the months work, the years should not be difficult.

For some reason, the [Number of Customers] is registering as a Dimension. I move it to Measures and add it to Rows, it defaults to the COUNT. I change this to SUM, add the [Time Entered] to Columns, and truncate it to the month (DATETRUNC('month', [Time Entered])). When I do this, the values of Number of Customers are summed in the visualization though, so January shows up in the line chart as the total sum of all of the values in the month of January (obviously much higher than the actual number of customers the company has at the *end* of January).

I'd like to get the last value for each month as representative of that month, and then also calculate growth rates based on those last values for each month.

I've spent about an hour going through the forums. Every users' case seems to be different. I'm a bit new when it comes to calculated fields and dates so if anyone has a useful answer to this, I will greatly appreciate it.

Thank you.

• ###### 1. Re: Growth Rate for Running table of totals

Hi Margaret

If I understand your request correctly, this formula works.

// Create year month

// Calculate End of Month (The largest date in the year/month)

[End of Month]

{fixed [Date (Month / Year)]:max([Date])}

Overview: Level of Detail Expressions

// Create filter to pick up only end of month.

[Filter End of Month]

if [End of Month]=[Date] then "show" else "hide" end

Growth Rate

Use quick table calc.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Growth Rate for Running table of totals

Thank you so much Shinichiro. This worked perfectly and I now understand a new way to use filters

• ###### 3. Re: Growth Rate for Running table of totals

You are welcome.

Thanks,

Shin

• ###### 4. Re: Growth Rate for Running table of totals

Hi Shin,

I have another visualization I'm working on that requires looking at the second row of data for a given Post ID (according to Time Entered). I originally was using the calculation [Time Entered Local] = { FIXED [Post Id] : MIN([Time Entered Local]) }

But I want the second row, not the first row (which is essentially the minimum). Is there a quick adjustment for this?

Thank you!

Margaret

• ###### 5. Re: Growth Rate for Running table of totals

Try

[Time Entered Local] = {FIXED [Post Id] : min (if [Time Entered Local] > { FIXED [Post Id] : MIN([Time Entered Local]) } then [Time Entered Local] end)}

or something similar.

I hope you understand the direction.

Thanks,

Shin

1 of 1 people found this helpful