How to see active subscriptions without copying rows?

Bradelle

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?

ColemenWilson

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.

david_cunningham

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.

GrantSmith

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?

ColemenWilson

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.

Bradelle

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

david_cunningham

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.