Struggling with something basic... SUM()

[Jake_Naude]

Evening,

I’m trying to SUM() a list with a Number type VC expression and I don’t get why it isn’t working…

• This expression returns a list of two rows (22 & 36)
  The value of [cms_estimate] in row 22 is 5
  The value of [cms_estimate] in row 36 is 6

MIN(
			SELECT(
				survey[_RowNumber],
				AND(
				[lcs] = [_THISROW].[lcs],
				[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
				[survey_number] = [_THISROW].[survey_number]
				))
			)

• I’m trying to SUM() the [cms_estimate] column rows so I pin this to the front:

SUM(
	SELECT(
		survey[cms_estimate],
		[_RowNumber] = 
  ...

  )
)

• but the following gives me an answer of zero when I’m expecting to see eleven

SUM(
	SELECT(
		survey[cms_estimate],
		[_RowNumber] = 
			MIN(
			SELECT(
				survey[_RowNumber],
				AND(
				[lcs] = [_THISROW].[lcs],
				[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
				[survey_number] = [_THISROW].[survey_number]
				))
			)
		)
	)

Please could someone point out what I’m doing wrong…?.. Cheers…

[GreenFlux · Accepted solution]

Jake_Naude:

Please could someone point out what I’m doing wrong…?.. Cheers…

Just a guess, but you have two nested SELECT()s, so maybe try [_THISROW-1].

[Jake_Naude]

GreenFlux:

Just a guess, but you have two nested SELECT()s, so maybe try [_THISROW-1] .

Hi @GreenFlux,

Hole-in-one! That did the trick…! Many thanks…

SUM(
	SELECT(
		survey[cms_estimate],
		[_RowNumber] = 
			MIN(
			SELECT(
				survey[_RowNumber],
				AND(
				[lcs] = [_THISROW-1].[lcs],
				[select_cabinet_to_survey] = [_THISROW-1].[select_cabinet_to_survey],
				[survey_number] = [_THISROW-1].[survey_number]
				))
			)
		)
	)

.
.
Cheers…

[GreenFlux]

Glad to help!