Solved

Joining/appending transaction lists

  • 4 January 2023
  • 1 reply
  • 130 views
agnar
Rank
Userlevel 1
Badge +1
  • agnar Seasoned Pigmenteer
  • Seasoned Pigmenteer
  • 13 replies

Is there a way to create a metric that joins/appends values from two separate transaction lists? 

List 1 would for example be most popular colour by product and month in 2022 and List 2 would have most popular colour by product and month in 2023. In my metric I want to join the two into a single metric.

The values are dimensions, i.e. not amounts. Using a + works when working with amounts

List1.Quantity[by lastnonblank: List1.ProductID, List1.Month]
+
List2.Quantity[by lastnonblank: List2.ProductID, List2.Month]

but doing this with dimensions gives me an error:

Error: Arithmetic operators only accepts same type arithmetic inputs after implicit casting, received Dimension (Competence) operands

The reason I’m not using the same list for everything is due to List 2 having additional data that wasn’t available when we built the original model, and it having an automated import. And I’m trying to find shortcuts to avoid having to rebuild everything :) 

icon

Best answer by francois 4 January 2023, 12:14

View original

1 reply

francois
Rank
Userlevel 6
Badge +14

Hi Agnar,

The issue here is Pigment doesn’t know what to do if it finds different values in the same ProductID and Month intersections - that’s why you used LASTNONBLANK in your BY by the way.

To counter this, I’d probably use IFBLANK to give Pigment an order of priority of what values to take first.

IFBLANK(
  List1.Quantity[by lastnonblank: List1.ProductID, List1.Month],
  List2.Quantity[by lastnonblank: List2.ProductID, List2.Month]
)

This would take the List1’s value first, and then List2 if there is no value there. This means in the case of two values for an intersection, Pigment knows it should take List1’s value.

Since you clearly know the time frames for each transaction, you could also use something like

IF(Month.Year = Year."FY 22",
  List1.Quantity[by lastnonblank: List1.ProductID, List1.Month],
  List2.Quantity[by lastnonblank: List2.ProductID, List2.Month]
)

Hope this helps!

François (Pigment)

Reply


Ask your question here!

Terms & ConditionsCookie settings