Hello Everyone. I am a beginner in Pigment and I am trying to replicate my excel model within pigment. My current excel model will take a unique count of my order number (order number replicates due to multiple sku in one order) and mark the order as 1 and the duplicate as 0. Example: (if A2=A1,0,1). This gives me the unique number of orders in my transaction list. I cannot reference a cell directly using the if formula in pigment as compared to excel. Is there a an easier way to do this in pigment?
Page 1 / 1
Hi,
You can do a COUNTUNIQUEOF(Transactionlist.property) in a metric or the playground
Hi @Juned.husain,
if you need a count indicator on the transactions itself you could have a property, e.g. “Count order_number” with formula CUMULATE(1, <transactionlist>.order_number). This would add 1 for each occurrence of the order_number. Filtering for “Count order_number” = 1 would give you the records with the first occurrence of the order_number, for instance, similar to your Excel model. Duplicates would all have a number greater than 1.
Thank you @Nathan @oliverlee for replying. My solution leans more towards what @oliverlee has suggested. I tried what you suggested @oliverlee, but it seems to be giving me an error
Hi @Juned.husain yes sorry the formula should be
CUMULATE(1, ',TR] Sales (BI Data Mart)', ',TR] Sales (BI Data Mart)'.order_number)
so that it cumulates across the whole list items, and groups it by order number
Hello @oliverlee the formula you shared works. Thank you
hey, not sure what you are trying to do though.
While it works, cumulate it a relatively heavy function, to be avoided on medium volume (tens of milions) when possible, especially if there exist other options
