https://www.gooddata.com/ logo
Join Slack
Powered by
I’m back with one of my nightly questions. This ti...
# gooddata-platform
t
I’m back with one of my nightly questions. This time I have a many-to-many relationship in my model (I know, not ideal, but needed in tagging cases). I have a 
Fact Conversions
with a 
Conversion Created Day
and a 
Dim Features
with a 
Feature Release Date
, where a conversion can be related to multiple features via the map table. Since the Feature dimension is very small, this is no big deal computation-wise. Now I want to build a Metric 
Conversion Count for Features not older than 6M
that just looks for each conversion if there is at least one feature related to it that was released max 6M before the conversion creation. I later want to show this metric either as an absolute or in relation to total conversions. I tried to do it via the BY keyword, but so far none of my metric attempts were applicable. They never show up in the available selection…
m
Hi Thomas, I think the easiest way could be to just turn the edge between 
Fact conversions
and 
Map conversion to feature
to a many-to-many edge in the GoodData logical model. This part of the model seems to be an ideal candidate for that, with no complications. Then any filter applied to 
features
(including a date filter) will get propagated to to 
Fact conversions
via the 
map
table.
🙌 1
t
thanks, I’ll give that a try. I never considered the edges at all.
@Michal Hauzirek so, I managed to set up the many-to-many edge, but I still didn’t manage to build a functioning metric 
Conversion Count for Features not older than 6M
or in fact any other metric combining the two data sets e.g. 
Min days between conversion creation and release of a feature
. Could you please provide some examples that help me build those?
m
I believe “Conversion Count for Features not older than 6M” should work with metric like this if you enable the M:N edge between Fact Conversion and Map datasets: 
SELECT COUNT(Conversion Key) WHERE  Month/Year (Feature release date) >= THIS-6
The “Min days between conversion creation and release of a feature” was a little more tricky, but I think now I have it as well:
Copy code
SELECT MIN(
  SELECT 
    (SELECT Date (Feature Release Date) - Date (Conversion Created Date) BY Map Key)
  BY Feature Key, Conversion Key, ALL OTHER
)
the very inner metric performs the difference of the dates on the Map Key level. The next level with 
BY Feature Key, Conversion Key, ALL OTHER
seems to be needed there to make it work properly in case both of the keys are not present in the report. And the topmost MIN is just to get minimum. I hope this helps.
t
Will try that now! Thanks for following up with that!
3 Views
Open in Slack
PreviousNext
Powered by