Set expression

[upaliwije]

=sum( {<[To_YEAR] = {"$(=Only([R_YEAR]+1))"},[To_MONTH]={"$(=Only([R_MONTH]))"},POL_TYPE={'R'}>} [PREMIUM] )

Pls help me to modify this expression to show results To_MONTH >= R_MONTH

=sum( {<[To_YEAR] = {"$(=Only([R_YEAR]+1))"},[To_MONTH]={"$(>=Only([R_MONTH]))"},POL_TYPE={'R'}>} [PREMIUM] )

I have written it like above but not working

[jerem1234]

Try this:

=sum( {<[To_YEAR] = {"$(=Only([R_YEAR]+1))"},[To_MONTH]={">=$(=Only([R_MONTH]))"},POL_TYPE={'R'}>} [PREMIUM] )

Hope this helps!

[upaliwije]

It is not working

[jerem1234]

What do your values for R_Month look like and what is Only([R_MONTH]) supposed to return?

If you posted your app or a sample app demonstrating your problem, I could help you more.

[upaliwije]

Suppose my To_Month= Jan  so when I select Jan as To_Month  the premium should be summed for the Months More than or equel to R_month. R_month is my Transaction month

[jerem1234]

Im supposing your To_Month and R_month fields are not in a date format, so doing >='Mar' doesnt work. You should change your To_Month field in your script like:

Month(date#(To_Month, 'MMM')) as To_Month

Then do the same for your R_month. Then you should be able to compare for '>='.

Hope this helps!

[upaliwije]

But when I remove > sign it is working. It is a question of using >= instead of = sign

[#8 · Accepted solution]

Try this:

sum( {<[To_YEAR] = {'$(=max([R_YEAR])+1)'},[To_MONTH]={'>=$(=max([R_MONTH]))'},POL_TYPE={'R'}>} [PREMIUM] )

regards.

[jerem1234]

Yes, when you use "$(=Only([R_MONTH]))", it probably returns a string like Mar. Then coupled with the quotes you have returns "Mar". Then with the set analysis, it goes through your field To_Month and matches the string value using the "=" sign. But when you are using the operators like >, <, >=, <=, you need number values to compare, not strings.

Another attempt:

=sum( {<[To_YEAR] = {"$(=Only([R_YEAR]+1))"},[To_MONTH]={">=$(=num(Only([R_MONTH])))"},POL_TYPE={'R'}>} [PREMIUM] )

If that doesnt work, what you can do to test is put that expression in a straight table. Then if you don't touch the label, the formula should appear in your header for that column and then you can see what the set analysis is returning.

Let me know what it returns for {">=$(=Only([R_MONTH]))"} part in that header of your expression.

Find attached as example.