topic Re: Set expression in QlikView

Set expression

upaliwije — Tue, 11 Feb 2014 16:34:40 GMT

=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

Re: Set expression

jerem1234 — Tue, 11 Feb 2014 16:37:53 GMT

Try this:

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

Hope this helps!

Re: Set expression

upaliwije — Tue, 11 Feb 2014 16:41:49 GMT

It is not working

Re: Set expression

jerem1234 — Tue, 11 Feb 2014 16:46:42 GMT

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.

Re: Set expression

upaliwije — Tue, 11 Feb 2014 16:54:54 GMT

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

Re: Set expression

jerem1234 — Tue, 11 Feb 2014 17:11:30 GMT

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!

Re: Set expression

upaliwije — Tue, 11 Feb 2014 17:17:32 GMT

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

Re: Set expression

Tue, 11 Feb 2014 17:22:08 GMT

Try this:

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

regards.

Re: Re: Set expression

jerem1234 — Tue, 11 Feb 2014 17:37:14 GMT

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.