Skip to main content
Solved

How to do Unique Count?

  • January 28, 2025
  • 6 replies
  • 315 views
J
Apprentice Author

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? 

 

Best answer by oliverlee

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

 

6 replies

Nathan
Employee
Forum|alt.badge.img+12
  • NathanEmployee
  • Employee
  • 227 replies
  • January 28, 2025

Hi,

 

You can do a COUNTUNIQUEOF(Transactionlist.property) in a metric or the playground

oliverlee
Master Helper
Forum|alt.badge.img+12
  • oliverleeMaster Helper
  • Master Helper
  • 50 replies
  • January 28, 2025

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.

J
Apprentice Author
  • Juned.husainApprentice Author
  • Author
  • Apprentice Author
  • 3 replies
  • January 31, 2025

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
 

 

oliverlee
Master Helper
Forum|alt.badge.img+12
  • oliverleeMaster Helper
  • Master Helper
  • 50 replies
  • Answer
  • January 31, 2025

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

 

J
Apprentice Author
  • Juned.husainApprentice Author
  • Author
  • Apprentice Author
  • 3 replies
  • January 31, 2025

Hello ​@oliverlee 
the formula  you shared works.
Thank you

Nathan
Employee
Forum|alt.badge.img+12
  • NathanEmployee
  • Employee
  • 227 replies
  • January 31, 2025

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


Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement