Net revenue retention

TrentB
TrentB Member
in Magic ETL

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 Domo Employee
    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

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • Chips
    Chips Domo Employee

    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?

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • TrentB
    TrentB Member

    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
  • Chips
    Chips Domo Employee

    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?

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • TrentB
    TrentB Member

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

     

  • Chips
    Chips Domo Employee

    What value does the following beastmode return? A number?

     

    month(`Invoice date`)

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • TrentB
    TrentB Member

    that is correct. 

    month(`Invoice date`) returns the month number

  • TrentB
    TrentB Member

    still looking for a solution here

  • Chips
    Chips Domo Employee
    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

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories