Solved

Sum by current year of subscription rather than calendar year

  • 10 January 2023
  • 3 replies
  • 34 views

Hi team,

I have a data model where we keep track of the customers accounts as well as the campaigns created by them.

Customers have subscription_start_date and subscription_end_date as date dimensions

and

Campaigns have created_at as date dimensions

Further, Campaigns also have a Fact called as Volume

Subscriptions can run into multi years and the year (not the calendar year) of subscription starts from subscription_start_date

Eg.

A customer ABC, subscription_start_date = 04-Jul-2019, subscription_end_date= 04-Jul-2025

Current year volume would be sum of volume of all the campaigns created after 04-Jul-2022

 

I want to create a metric that gives me the Volume for the current year of subscription

 

There can be different customers with different subscription_start_date

 

icon

Best answer by Joseph Heun 10 January 2023, 14:16

View original

3 replies

Userlevel 3

You will need to use some date arithmetic to find the min/max of each period you are looking for: 

 

https://help.gooddata.com/classic/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/finding-min-and-max-dates-using-date-arithmetic

 

This will help you find the duration of specific events, but please keep in mind the specific connection points in your LDM as this will affect if the metrics can be built or not.

You will need to use some date arithmetic to find the min/max of each period you are looking for: 

 

https://help.gooddata.com/classic/en/dashboards-and-insights/maql-analytical-query-language/maql-use-cases-and-tutorials/finding-min-and-max-dates-using-date-arithmetic

 

This will help you find the duration of specific events, but please keep in mind the specific connection points in your LDM as this will affect if the metrics can be built or not.

Didn’t understand, can you share an example as per my sample fields?

Userlevel 3

If you are looking for the duration of time between two dates, you will need to find the MAX date and then the MIN date in two separate metrics. Then you will create a third metric using simple arithmetic to find the duration between both of those dates. It’s a bit difficult to write a complete metric without knowing the LDM or connection points involved, but the previous documentation provides a clear example. 

 

There is also a great free course on how to work with maql and set up metrics on our GoodData University

Reply