topic Re: Set Expression in QlikView

Set Expression

upaliwije — Sat, 15 Feb 2014 05:27:34 GMT

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

Re: Set Expression

Sat, 15 Feb 2014 08:29:33 GMT

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.

Re: Set Expression

whiteline — Sat, 15 Feb 2014 18:55:25 GMT

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] )