topic Set Expression in QlikView

Set Expression

upaliwije — Fri, 14 Feb 2014 15:00:55 GMT

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

Re: Set Expression

MK_QSL — Fri, 14 Feb 2014 15:16:21 GMT

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*'}

Re: Set Expression

Fri, 14 Feb 2014 15:17:49 GMT

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

Re: Set Expression

maxgro — Fri, 14 Feb 2014 15:29:50 GMT

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

Re: Set Expression

peschu123 — Fri, 14 Feb 2014 15:58:42 GMT

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

Re: Re: Set Expression

maxgro — Fri, 14 Feb 2014 20:04:54 GMT

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

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

Re: Set Expression

Sokkorn — Sat, 15 Feb 2014 02:06:40 GMT

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