Hi Jose,

The order and where you do MODIFIER / FILTER has an importance:

• you are doing your first filter on the second metric  which doesn’t have the dimension 
• you are doing the FILTER after the BY, so once the data has been aggregated to the dimensions, stopping you from filtering on the transaction lists

Do like this:

Transaction.PropertyeFILTER:whatyouwant]aBY:transaction.property] - ‘Billsmonth Spread’pFILTER:ISDEFINED()]

ps: it’s probably better for speed that Transaction.PropertynFILTER:whatyouwant]aBY:transaction.property] is in its own metric, to isolate this calculation and allow it to be ran independently by the engine

Hi Nathan,

Thanks for your reply! 

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!

BR,

Jose

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.MaillBY LASTNONBLANK: BillsMonth.Mail]) for your BillsMonth transaction

• filter the transactions based on the conditions that the mail exists in both, e.g. RequestsMRR.MRRuFILTER: RequestsMRR.CustomerEmail.ExistsInBillsMonth]nBY: RequestsMRR.CustomerEmail, RequestsMRR.Month] or BillsMonthSpreadsFILTER: 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.

Hi Francois!

Thanks for your support!

I tried to create the boolean for the Mail dimension, but I get the following error:

Thank you very much in advance, 

BR,

Jose

It seems you’re trying to enter the formula in the Name column

Hello Francois!

I was able to create the booleans ExistsInBillsMonth and ExistsInRequestsMRR, but when I apply the formula with the filters , I get all blanks.

I also tried just performing the subtraction without the filter and then doing the filter in another metric and I got the following problem.

Thank you very much in advance!

Jose

Hello Francois,

I wanted to know if you have any suggestions on why are the formulas not working for the case above?

Thank you very much in advance!

Best,

Jose

Hello Jose, 

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.
 

Hello Ben A,

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.

Thank you very much in advance!

Jose

Hello Jose
It is because the “month” column in your “BillsMonths” transaction list is empty. If you fill it, you should get values.