Set Expression

[upaliwije]

Hi Friends

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

I have written the above expression in the attached file and it is working, But I want to exclude the following records only

When the use select R_Year as 2013 and R_Month as Jan

I want to show only  records where To_Month<> R_Month (R_Month=Jan and To_month= any month other than Jan)

In other words 1st 11 records shown in my my pivot table(attached) should be excluded and all other records must remain as it is

Please help me to rewrite the  expression

Thanks

[#2]

Hi,

It seems, you can solve this problem by using the variables. Follow the below steps.

1. Create variable vVar1 and assign ',' as text (Don't use = sign to assing this text).

2. Create another variable vVar2 and assing string as

          =chr(39) & Replace(GetFieldSelections(R_MONTH), ', ',vVar2) & chr(39)

3. Change your set expression as below:

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

Probably you may get better solution than this from our folks.

[whiteline]

Hi.

It seems that the case is clear for you. Now you should write the same logic using set analysis syntax. Keep R_Month selection (so you can skip it in expression) and keep only those To_Month where To_Month<> R_Month.

For example:

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