Variable 'Where' Question

88 Views Last edit Jan 15, 2020 at 03:43 PM 2 rev

Looking for a solution to allow for a count of rows based on specific text from a variable in the row.

=If([Order]) InList ("Text1"; "Text2"; "Text3") Then ([Detail Line]) - This returns the specific line text

=If([Order]) InList ("Text1"; "Text2"; "Text3") Then Substr ([Detail Line];20;55) - This returns the text withing the line

=Substr ([Detail Line];1;Pos ([Detail Line];",")-1) - This returns only the text needed at the comma

For testing I added a row count, Line count and a count.

=RunningCount([DT/Tm];([CustNumber])) - This is the Row Count

=RunningCount(LineNumber();([CustNumber])) - This is the line count (returns the same as the Row Ct)

=Sum([RowCt]) Where([ct]=2) ForEach([CustNumber]) - Returns a 1 for every row where there is a row count of 2.

I then need to count the rows where the text from the detail line changes from row 1 to row 2. As an example, if row 1 is Green and Row 2 is Red then 1. If Row 1 is Green and Row 2 is Green then 0. If Row 1 is Black and Row 2 is Orange then 1. Basically the text in each row changes for each order. A separate variable would be needed for each text change.

I think that the Where function is needed but are unclear on the syntax. Is this possible?