Set Analysis Syntax?

[cbaqir]

I am trying to get the sum of MTD_BUDGET_AMT for the most recent month in which MTD_GL_AMT >0. Therefore, I think I need to use Set Analysis with:

Sum(MTD_BUDGET_AMT)

This expression gives me the correct date of 9/30/16:

=Monthend(Max({1<MTD_GL_AMT={">0"}>}TRX_DATE))

I'm trying to figure out the correct syntax. I would expect to see 48,478,839

I've also tried creating a variable with the above Monthend expression : vCurGLMonth but this doesn't seem to work either.

=sum({$<TRX_DATE={$(=vCurGLMonth)}>}MTD_BUDGET_AMT)

Thoughts?

Thanks!

Cassandra

[Anonymous]

Try this

[swuehl · Accepted solution]

Maybe just format your value according the field format you want to use in the set expression:

=sum({$<FISCAL_MONTH_YEAR={"$(=Date(vCurGLMonth,'YYYY/MM'))"}>}MTD_BUDGET_AMT)

[cbaqir]

Thanks but I need it working with the variable to select the right date. Hard coding the date won't work.

[cbaqir]

thank you!

[tresesco]

Try like:

=sum({$<TRX_DATE={'$(vCurGLMonth)'}>}MTD_BUDGET_AMT)