Net revenue retention

TrentB

I am trying to calculate a net rev retention from X month to Y month (typically 1 months prior).  This calc would compare revenue from all the accounts that paid us in X month to what those same accounts paid us in subsequent months.  In the example below, the sum of the months would only campare accounts A,B, and C since D and E are new logos.  the bar chart would only display A,B,C accounts but would show the sum trend for the following months.  I need a filter or something that says "If account was invoiced in Jan, only display those on the bar chart" but display all the susequent months not just the Jan data.  How would i do this in Beast mode?  

 

account	Jan	Feb	Mar	Apr
A	100	150	150	200
B	50	25
C	200	200	175	225
D		125	150	200
E				150

 

I tried

CASE WHEN MONTH(`InvoiceDate` ) = 9 THEN 'true' else 'false' END

but this only shows Sep data on the bar chart

Chips

Sorry for the delayed response @TrentB, I have tried to approach this a couple of different ways using only beastmode and am unable to come up with a viable solution.

 

I think you have two options:

1. Use a dataflow to tag each account with a "Month of First Invoice", then use that field as a Series in a multi-line card

2. Leave the dataset as is, and use a "Nested Grouped Bar" with Amount as the Y-Axis and "Month of Invoice" as your X-Axis. From there, use the interactivity native to the card to select only accounts that are Active in the month you want to visualize. The Nest shadow will show you the totality of those accounts over time, and the individual bars would tell the story of each individual account.

 

Inelegant I know, but the best I can come up with.

 

Chips

Chips

What does your dataset look like?

 

MONTH   CUSTOMER    VALUE

Jan            A                     100

Feb           A                     150

 

Or does it look more like your table below? And is the date value just a month or is it the full date?

TrentB

the data set includes a row for each account invoice.  

 

 

invoice date	account	amount
Jan	A	$       100
Jan	B	$         50
Jan	C	$       200
Feb	A	$       150
Feb	B	$         25
Feb	C	$       200
Feb	D	$       125
Mar	A	$       150
Mar	C	$       175
Mar	D	$       150
Apr	A	$       200
Apr	C	$       225
Apr	D	$       200
Apr	E	$       150

Chips

And the invoice date is a text field with the month abbreviation (i.e. "Jan", "Feb" etc.)?  Or is it a date or numerical value?

TrentB

Invoice date field is a date field  not text or numeric and looks like "2018-Jan"

 

Chips

What value does the following beastmode return? A number?

 

month(`Invoice date`)

TrentB

that is correct. 

month(`Invoice date`) returns the month number

TrentB

still looking for a solution here

Chips

Sorry for the delayed response @TrentB, I have tried to approach this a couple of different ways using only beastmode and am unable to come up with a viable solution.

 

I think you have two options:

1. Use a dataflow to tag each account with a "Month of First Invoice", then use that field as a Series in a multi-line card

2. Leave the dataset as is, and use a "Nested Grouped Bar" with Amount as the Y-Axis and "Month of Invoice" as your X-Axis. From there, use the interactivity native to the card to select only accounts that are Active in the month you want to visualize. The Nest shadow will show you the totality of those accounts over time, and the individual bars would tell the story of each individual account.

 

Inelegant I know, but the best I can come up with.

 

Chips