nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Formula for Number of Unique Categories in Column

Martin

I am looking for the column formula to provide me with the count of unique categories in a column (maybe based on another columns categories). For instance, if a column has

then I would like to get back the value 3.

Thanks

Accepted answers

txnelson

Try this formula.......of course, swap out the :Flow for the column you want to count the categories for

Summarize( x = by( :FLOW ) );
N Items( x );

If the data table is really large, you may want to use this more efficient version

If( Row() == 1,
	Summarize( x = by( :Flow ) );
	y = N Items( x );
);
y;

All comments

Martin

I think I remember the Summation formula being used for this, but my memory fails me.

txnelson

Try this formula.......of course, swap out the :Flow for the column you want to count the categories for

Summarize( x = by( :FLOW ) );
N Items( x );

If the data table is really large, you may want to use this more efficient version

If( Row() == 1,
	Summarize( x = by( :Flow ) );
	y = N Items( x );
);
y;

Martin

This is PERFECT! Thank you Jim!

aronwilson

I see how this would work for a script to create a column, but I've created a datatable that I update from a database and I'd like to have a column in that table with a formula to calculate the number of unique categories. Is there a way to do that directly in a column formula instead of though a script?

I found a hack using using Def() and Col Sum but it only works because i'm looking at a sorted field that's a unique id with numbrers. It doesn't work for actual text variables. Is there a better solution for that?

Here is the hacked formula:

Col Sum( Dif( :customer_id ) > 0 )

Thank you,

Aron

txnelson

The stated formula from my last response should do what you want.

If( Row() == 1,
	Summarize( x = by( :Customer_ID) );
	y = N Items( x );
);
y;

Just cut and paste this into the formula editor

Martin

What I always forget, is that a column formula can actually be an entire mini script; with many semicolons and lines of code.

aronwilson

That worked great! I was thrown off by the red underline, I thought that meant it wasn't going to work.

I'm not actually sure I've ever tried pasting a formula directly in the column before, I thought I had to rebuild it using the explicit formula column functions... this is a huge improvement. Thank you so much for the quick reply!

Aron