topic Getting a Cumulative Sum Count based on multiple column conditions in Discussions

Getting a Cumulative Sum Count based on multiple column conditions

PivotalWeasel30 — Tue, 30 Jan 2024 17:03:15 GMT

Hi,

I am trying to incrementally count rows in a dataset based on matching conditions in three other columns. Essentially if the rows in Column A, B, and C match, then Column D = 1; when Column C changes (Depth) but Column A and B stay the same, Column D = 2. I need this count to restart from one when Column A (Site ID) or B (Year) change. I have gotten this far with the following formula:

Col Cumulative Sum( 1, :Site Code, :Year, :Depth )

This gives me the following:

Which is close, but I need Column D (Sample #) to look like this

My apologies if my explanation is unclear, but hopefully the pictures clarify what I mean.

Thanks!

Re: Getting a Cumulative Sum Count based on multiple column conditions

PivotalWeasel30 — Tue, 30 Jan 2024 17:04:34 GMT

P.S. I know the images are from Excel, I just exported a subset of my data for clarity.

Re: Getting a Cumulative Sum Count based on multiple column conditions

jthi — Tue, 30 Jan 2024 17:08:43 GMT

You could combine Col Cumulative Sum with Col Min

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "B"})),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 1, 2, 2, 1, 1, 1, 1, 1, 1])),
	New Column("Column 3", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 2, 3, 1, 1, 1, 1, 2, 2, 3, 3]))
);

dt << New Column("R", Numeric, Continuous, Formula(
	Col Cumulative Sum(Row() == Col Min(Row(), :Column 1, :Column 2, :Column 3), :Column 1, :Column 2)
));