Hello, I'm trying to calculate a rate metric (3ds rate = # payments where 3ds was requested / # payments). I have two tables in data model -

PAYMENTS

(which has all payments) and

THREEDS

(which has only payments where 3ds was requested) joined on

PAYMENT_ID

. I created the rate metric in GoodData

SELECT COUNT({label/THREEDS.PAYMENT_ID}) / COUNT({label/PAYMENTS.PAYMENT_ID})

however it always returns 100%, I assume because GoodData does inner join when building the query so not all records in

PAYMENTS

table are counted. Can you advice how to modify the metric so that I would get the actual rate?

Hi Zuzana, The way it’s currently being done, it looks like your metric is counting the unique values for the same attribute on both datasets. Even if there are more total appearances of

Payment ID

on

Payments

, the unique count of IDs will still be the same as in

Threeds

, which results in a total of 1. I am not sure how your Data Model is constructed, but I would recommend using a different attribute for one of the counts - preferably, so that you are counting the primary key of both datasets. If there is still some ambiguity in the context of the COUNT, you may need to specify it via the USING keyword. I’d recommend taking a look at the Count Function article for some more info.

Hi Zuzana - would you mind sharing how does the GoodData Logical Data Model for your workspace look like (or at least this part with the two tables you mention). You said you joined those tables by PAYMENT_ID but in your query there seems to be two different attributes - that would suggest these tables are either not joined in the LDM or are joined by something else. Also do you only want the metric to display a single number rate or do you want to view this rate by some attribute (i.e. region) from any of those two tables?

Hello, thanks for your responses. I attached here the relevant part of our data model. The join between the two tables is on Payment ID. I would like to get the metric per day and also by other dimensions from the payments table. I have changed the metric to count different attributes and I get a correct result but when I add CREATED_AT the metric shows again 100%.

SELECT COUNT({label/THREEDS.ACTION_ID}) / COUNT({label/PAYMENTS.PAYMENT_ID})

Thank you Zuzana, I believe metric like this should work in your case:

SELECT
  COUNT({label/payment_id},{dataset/threeds}) 
  / 
  COUNT({label/payment_id},{dataset/payments})

(note that the exact identifiers might be different in your case. But the main difference from what you posted is that: • the first parameter in both COUNTs is the same - the paymentID (from payments) • the second parameter in both COUNTs is different: ◦ once it is dataset payments ◦ once it is dataset threeds The second parameter defines where (in which table) should it count the PaymentIDs.

Hi, unfortunately this solution also didn't work because I am not able to filter the visualization by

CREATED DATE

or any attributes from

PAYMENTS

😞 I think I will just add a 3ds flag to our Payments table which should solve this issue. Ideally I would like to be able to create this kind of flag in GoodData, something like

SELECT IF THREEDS.PAYMENT_ID IS NOT NULL THEN TRUE ELSE FALSE

and use this as an attribute in my metrics / visualizations. As this functionality is not supported at the moment, are there any plans to add it? Seems pretty standard in other tools, e.g. Tableau.

I think I see it now. I did not notice it before, but in your Data Model the “Payment” dataset is referencing the “Threeds” dataset. That would mean that each and every payment has a Threed and each Threed can have zero or more Payments. If I understand your initial description correctly, it is the other way around- not every Payment has a Threed but each Threed belongs to exactly one payment, correct? If yes, then you should make the relationship in the model the other way: Have an arrow pointing from Payments dataset to Threeds dataset. Please note that in GoodData LDM the arrow goes from primary key to the foreign key (which is the other way than in some notations). Also any filters are applied in the direction of the arrows. So if you change this you will be able to apply filter of any payments attribute or create date to the calculation.