topic Re: Set Analysis Syntax? in QlikView

Set Analysis Syntax?

cbaqir — Tue, 22 Nov 2016 17:05:38 GMT

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 — Tue, 22 Nov 2016 17:09:02 GMT

Try this

swuehl — Tue, 22 Nov 2016 17:11:00 GMT

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 — Tue, 22 Nov 2016 17:12:35 GMT

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

cbaqir — Tue, 22 Nov 2016 17:13:02 GMT

thank you!

tresB — Tue, 22 Nov 2016 17:16:28 GMT

Try like:

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