I created a new metric in which I subtract two metrics that have the same dimensions. Since the metrics are coming from different transaction lists that share the same dimension (customer email) , one frome Resquests MRR BE and the other from BillsMonths I dont share the same customer email in both metrics, and I want to perform the substraction of only those emails that are in both transactions. I tried using filters with ISDEFINED for the email in each transaction to see if the metric will only give the emails that are shared in both transactions but I couldn’t do the right formula.
If would be very helpful if you can point me in the right direction?
Thank you very much in advance!
Jose
Best answer by francois
Hi Jose,
Is the Mail property dimension-based? If so you’ll be able to compare the two transactions, but if it’s text-based you won’t easily be able to establish a link between the two transactions.
If it’s dimension-based, then here’s what I’d do:
in the Mail dimension, create a boolean for each transaction, e.g. Mail.ExistsInBillsMonth where the formula is ISDEFINED(BillsMonth.Mail[BY LASTNONBLANK: BillsMonth.Mail]) for your BillsMonth transaction
filter the transactions based on the conditions that the mail exists in both, e.g. RequestsMRR.MRR[FILTER: RequestsMRR.CustomerEmail.ExistsInBillsMonth][BY: RequestsMRR.CustomerEmail, RequestsMRR.Month] or BillsMonthSpread[FILTER: Mail.ExistsInBillsMonth AND Mail.ExistsInRequestsMRR]
The key being that you put the information whether it’s being used in both transactions in the Mail dimension directly.
ps: it’s probably better for speed that Transaction.Property[FILTER:whatyouwant][BY:transaction.property] is in its own metric, to isolate this calculation and allow it to be ran independently by the engine
However, If i do the filter in each one with ISDEFINED() right after the metric I will get the same result as if I didn’t do the filter. I have two metrics created , each one coming from a different transaction list , but using the same dimensions. My end goal is to create a new metric in which I will subtract one metric from another but only of the elements of the dimension that are in both transaction lists.
Please let me know if its more clear now, and thank you again for your support!
Is the Mail property dimension-based? If so you’ll be able to compare the two transactions, but if it’s text-based you won’t easily be able to establish a link between the two transactions.
If it’s dimension-based, then here’s what I’d do:
in the Mail dimension, create a boolean for each transaction, e.g. Mail.ExistsInBillsMonth where the formula is ISDEFINED(BillsMonth.Mail[BY LASTNONBLANK: BillsMonth.Mail]) for your BillsMonth transaction
filter the transactions based on the conditions that the mail exists in both, e.g. RequestsMRR.MRR[FILTER: RequestsMRR.CustomerEmail.ExistsInBillsMonth][BY: RequestsMRR.CustomerEmail, RequestsMRR.Month] or BillsMonthSpread[FILTER: Mail.ExistsInBillsMonth AND Mail.ExistsInRequestsMRR]
The key being that you put the information whether it’s being used in both transactions in the Mail dimension directly.
For your first formula, are you sure that all cells are blank ? Can you filter out blank values to check ?
For your second formula, this is quite normal to have a circular dependency error message as you are referencing the current metric’s name in the formula bar. You should reference the previous metric instead.
If I filter the non blanks I do get values for the MRR BE for customer mails that have both true ExistsInBillsMonth and ExistsInRequestsMRR, but for the second part in the substraction it seems to not find the values.
I am performing a substraction with the same dimensions but it says there is no data to display.
Even without the filters it seems to not take into account the second part of the formula.
