Different answers from a Set Expression

[463]

Does anyone know why I would receive different answers from a set expression by modifying whether I was using a string vice a date from a variable vice a text from a variable?

For example (1):
=Sum({1 <SH1.ProjectStructure={'1A'},SH1.LoadDate={'=vGetPreviousDTText'}>} [SH1.OG_Budget])

where vGetPreviousDTText is returned as a 'text' value and it formatted like '2/3/2026':
=Text(Date(Date#(Mid(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'), Index(CONCAT(DISTINCT {1} Date([SH1.LoadDate], 'YYYYMMDD'), '/'), Date(GetFieldSelections(SH1.LoadDate),'YYYYMMDD')) - 9, 8),'YYYYMMDD'),'M/D/YYYY'))

as compared to (2):

=Sum({1 <SH1.ProjectStructure={'1A'},SH1.LoadDate={'=vGetPreviousDT'}>} [SH1.OG_Budget])

where vGetPreviousDT is the same as above BUT, the 'Text' function at the beginning is removed. It returns a 'Date' formatted as '2/3/2026':

as compared to (3):

=Sum({1 <SH1.ProjectStructure={'1A'},SH1.LoadDate={'2/3/2026'}>} [SH1.OG_Budget])

where the value of '2/3/2026' is a straight string.

Only (3) returns the correct value.  (1) returns an incorrect value of '0'.  (2) returns an incorrect value of '100' (which, BTW, is a correct value for a date 7 days later.

TIA!

[bgerchikov]

Try $ expansion: SH1.LoadDate={'$(=vGetPreviousDT)'}