Net revenue retention

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?  

 

accountJanFebMarApr
A100150

 150

 200
B5025  
C200200 175 225
D 125150200
E   150

 

I tried

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

but this only shows Sep data on the bar chart

Best Answer

  • Chips
    Chips 🟠
    Accepted Answer

    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

Answers

  • 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?

  • the data set includes a row for each account invoice.  

     

     

    invoice dateaccountamount
    JanA $       100
    JanB $         50
    JanC $       200
    FebA $       150
    FebB $         25
    FebC $       200
    FebD $       125
    MarA $       150
    MarC $       175
    MarD $       150
    AprA $       200
    AprC $       225
    AprD $       200
    AprE $       150
  • 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?

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

     

  • What value does the following beastmode return? A number?

     

    month(`Invoice date`)

  • that is correct. 

    month(`Invoice date`) returns the month number

  • still looking for a solution here