Solved

Filtering to get only the dimensions shared in two transaction lists?

J
Rank
Userlevel 2
Badge +1
  • Jose Enthusiastic Pigmenteer
  • Enthusiastic Pigmenteer
  • 22 replies

Dears,

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

icon

Best answer by francois 29 August 2022, 10:53

View original

10 replies

Nathan
Rank
Userlevel 6
Badge +10

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.Property[FILTER:whatyouwant][BY:transaction.property] - ‘Billsmonth Spread’[FILTER:ISDEFINED()]


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

J
Rank
Userlevel 2
Badge +1

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

francois
Rank
Userlevel 6
Badge +13

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.

François (Pigment)
J
Rank
Userlevel 2
Badge +1

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

francois
Rank
Userlevel 6
Badge +13

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

François (Pigment)
J
Rank
Userlevel 2
Badge +1

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

J
Rank
Userlevel 2
Badge +1

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

Ben A
Rank
Userlevel 2
Badge +3

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.
 

J
Rank
Userlevel 2
Badge +1

 

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

Ben A
Rank
Userlevel 2
Badge +3

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

 

Reply


Ask your question here!

Terms & ConditionsCookie settings