Solved

Start End Date bar chart by Months

  • 1 February 2023
  • 8 replies
  • 147 views

  • Participating Frequently
  • 5 replies

Hi Folks !
I'm reviewing GoodData, and of course I'm here cause I have some really basic questions.

To make the example easy, I'have a data set with 3 fields user, start end date (subscriptions base data). I'm trying to build a simple chart in which each MM-YY as (X axis) and count the active subscriptions (Y axis).

 

user start end
1 jan-22 dec-22
2 jan-22 mar-22

 

 

result would be (bar char):

 

2 2 2 1 1
jan-22 feb-22 march-22 apr-22 … rest of months ...

 

 

The issue I'm encountering is that GoodData is associating the date with only 1 date field either start or end. How can achieve this?

I read date-attribute-arithmetic, if I understood correctly that won't solve my query, as it calculates the duration by a given granularity 

icon

Best answer by michal 2 February 2023, 20:07

View original

8 replies

Userlevel 3

Hi Mares, 

 

May I know how have you defined the metric? Have you trying dragging and dropping the corresponding attributes in Analytical Designer? I can also recommend our Free course about MAQL for further guidance: Getting Started with MAQL (gooddata.com)

 

-Moises

HI @Moises Morales thanks for reaching out so shortly. Currently I'm really novice, experiencing the tool and still reading the doc from GoodData. 

An example of a posible MAQL would be the following.

SELECT {label/date.month}
WHERE
{label/date.month}
BETWEEN {label/start_timestamp.month} AND
{label/end_timestamp.month}


Unfortunately as I stated, when trying to create a simple bar chart where I would display MM-YY (X axis) and the amount of actives subscriptions (Y-Axis), the X axis is associated with 1 of the two date fields (start or end) rather than using a calendar.

While creating the above metric, can not use it as a dimension naturally. Additionally date.month is a data island with no relationship at all in DML, thinking GoodData would do a cross join between both and come up with the result. Can not seem to grasp how to accomplish my original query.

Any thoughts? 

Userlevel 3

Hi Mares, 

 

Thank you for the details. Could you please check your LDM structure? You need to make sure that the correct connections were made so the metric can work as expected. Also, the metric definition is a bit unusual, you would normally break down a metric that returns numeric values (facts) by date dimensions; these dates can be added to the insight by dragging and dropping the corresponding attributes to the insight. Lastly I would recommend checking our documentation here: Connection Points in Logical Data Models | GoodData

 

-Moises

Here's how my model looks like, which I think is fairly simple. Above you can see I got, "subscriptions" table, just for context, a subscription has 2 records (subscription_type), one period status trial and subscribed with their own period start and end. A user can have several subscriptions over the years (just wanted to clarify that there's no primary key here).

 


I would like to replicate something similar to the following image, month-year contains all subscriptions that are in between start and end (red are trials, and blue are subscribed users). Meaning a user can be in several months-year

 

I would really appreciate some advice on how could I achieve this, as reading the doc's / forum could not find any example.

Userlevel 3

You would need to define a metric in your insight for it to be displayed in a chart other than a table. For example: SELECT SUM (subscription_id) and then you could break it down by adding the date dimension under “view by”. The kinds of insights available can be found here: Insight Types | GoodData

Hi @Moises Morales I do really appreciate the help. Unfortunately, I already tried that. The result of it, is that when using date as X-axis on (what you mentioned, "view by"), you need to associate one of the 2 dates, start or end.

 

When dragging the date to view by, GoodData UI forces you to select which field do you want to use. As per my model start or end date. Which using either of them would not resolve the KPI.

 

Given my first post example:
 

user start end
1 jan-22 dec-22
2 jan-22

mar-22



Using start date as view by the outcome would be the following: please take reference that bold values wouldn't even appear, as there are no subs which starts on other months.

 

2 0 0 0 0
jan-22 feb-22 march-22 apr-22 … rest of months ...

 

Using end date (same explanation as above).

 

0 0 1 0 1
jan-22 feb-22 march-22 … rest of months ... dec-22

 

 

Maybe GoodData does not handle out of the box this specific scenario. I tried to create a dissociated date table from the model, but as soon as I use it all other fields from the model become unavailable, which as stated by GoodData is the default behaviour in order to prevent users not to mix data sets.


 

Userlevel 2

Hi @mares, I am afraid that you are right that GoodData currently does not handle this type of transformation in MAQL. As you already found out, each date is treated as separate date dimension and in each insight you need to specify which date dimension to use. In your case you can view your data by start date or end date, but what you really want is to view it by some third date something like “continuous subscription date” and this is not in your data model.

 

So to display this type of visualization in GoodData, you would need to slightly modify your model and do some data pre-transformation (either physical in ETL or in a view under your dataset). You would be aiming to something like this::

display_month subscribed_user_id unique_row_id
jan-22 1 1
feb-22 1 2
mar-22 1 3
apr-22 1 4
...    
jan-22 2 5
feb-22 2 6
mar-22 2 7

 

That means pre-calculate data in SQL so that for each month for which a user has an active subscription, there is a row with user_id in it. You could do this for example by performing a JOIN of your table to a table with a list of months using “WHERE months.display_month BETWEEN users.start AND users.end” (the unique_row_id is there just because GoodData Cloud currently requires unique identifier in each dataset).

 

With this data structure you can use this table as a dataset in your GoodData logical data model and then in your insight simply use display_month as your date and metric “SELECT COUNT(subscribed_user_id)” to return a distinct count of user_ids that were subscribed in that period.  Since the COUNT works as distinct count in GoodData, in this case it will work even if you aggregate to quarter or yearly level - each user will be counted only once even if they were subscribed in multiple months.

 

I hope this helps.

Hi @michal, thanks for the confirmation, it's most helpful, I ended doing exactly what you mention.
 

-- demo
SELECT *
FROM (
SELECT SEQUENCE(
--creates an array seq based on (start & end) * interval type
current_date,
date_add('year', 1, current_date),
INTERVAL '1' MONTH
) AS seq_month
)
CROSS JOIN unnest(seq_month) AS auto_gen(month)


Basically the code above  (representation) generates a row by the interval you specify. Wanted to share the approach as it might help someone else topping with the same issue.

Thanks again for the insights @Moises Morales & @michal 

Reply