How to see active subscriptions without copying rows?

Options
Bradelle
Bradelle Member

I have a dataset that has subscription data (Id number, Start Date, End Date) and I need a way to see how many are active on any given date.

A line chart with the date on the X axis and a Distinct Count of the ID as the Y axis would be my preferred visualization. Using start date or end date as the x axis does not work though.

I can't just join the data set to a daily calendar because that would be way to many rows.

Any one have any ideas?

Best Answer

  • david_cunningham
    edited May 2 Answer ✓
    Options

    If you only need filters for a couple of columns, you could always just include those in your group by defintion. This would let you implement the above solution, aggregating by day, while still allowing you to filter by subscription type (or other columns you may need). Although you wouldn't have all your columns for drilling. So something to keep in mind when picking which columns to group by.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • GrantSmith
    Options

    due to how Domo processes data it needs a record for each day of the subscription.
    Can you get a record for each date and the aggregate the totals in an ETL per day or do you need to filter your data?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ColemenWilson
    edited May 2
    Options

    You can join with a calendar, and then aggregate to the date in the ETL to reduce the row count. You'd sacrifice filtering (on the card) for row count savings, but if row count is a concern then that would be my suggestion.

    If I solved your problem, please select "yes" above

  • Bradelle
    Bradelle Member
    Options

    Unfortunately I need filtering (and drilling) for types of subscriptions.

  • david_cunningham
    edited May 2 Answer ✓
    Options

    If you only need filters for a couple of columns, you could always just include those in your group by defintion. This would let you implement the above solution, aggregating by day, while still allowing you to filter by subscription type (or other columns you may need). Although you wouldn't have all your columns for drilling. So something to keep in mind when picking which columns to group by.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**