Set Expression

[upaliwije]

I have Created the following expression

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

But It is not working. I attach herewith the file. What I really need is the following calculation

When User Select R_Year=2013  and R_Month=Jan

The Result should be To_Year=R_Year+1(2014)

                                To_Month=R_Month(Jan)

                                 Type+'F'

                                  and sum of Premium

Please help me to write the correct Expression

[MK_QSL]

Sorry if I am underlooked but haven't found any POL_TYPE in your Database/Apps.

There is a POL_NO and you can use like

POL_NO = {'*F*'}

[#3]

The Pol_Type variable doesn't exist in the attached file. Have you tried double quotes (" ") around your expressions within set analysis?

[maxgro]

your expression

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

changed to

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

[peschu123]

Hi Massimo,

I wanted to answer this:

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

Anyway... can you explain why $(=date(max(R_MONTH ),'MMM')  ) is necessary? I thought the fields would be already formatted as date/integer... The final data is bit too confusing to check the difference ^^ count of BCOD stays the same.

Regards Peter

[maxgro]

Hi Peter,

I checked my answer (To_month=.... is incorrect), it should probably be

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

or

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

Thanks for letting me know my mistake

[Sokkorn]

Hi Upali,

Here is your set expression

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

Base on your attached file, I don't find POL_TYPE field.

See, Max(R_MONTH) will return 1,2,3,.... mean that in number format. So for me, there are two reason that your set expression not work

    1. No field POL_TYPE

    2. To_MONTH content this value Jan, Feb, Mar,... while Max(R_MONTH) return 1,2,3,... then comparison will become fales.

Solution:

    1. Omit POL_TYPE={'F'} from set expression

    2. Max(R_MONTH) should return Jan, Feb,... not 1,2,3.., by use this expression =Pick(Max(R_MONTH),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Finally, your set expression should be

=Sum({$<R_YEAR=, R_MONTH=,

[To_YEAR] = {"$(=Max(R_YEAR)+1)"},

[To_MONTH]= {"$(=Pick(Max(R_MONTH),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))"}

>} [PREMIUM])

See attached file.

Regards,

Sokkorn