- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Getting a Cumulative Sum Count based on multiple column conditions
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!
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Getting a Cumulative Sum Count based on multiple column conditions
P.S. I know the images are from Excel, I just exported a subset of my data for clarity.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Getting a Cumulative Sum Count based on multiple column conditions
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)
));
-Jarmo