Date in Set Expression

upaliwije — Sun, 16 Feb 2014 14:53:31 GMT

In My attachment I have following dimension fields

R_Year,R_Month,To_Year, To_Month

when I select R_Year+2013 and R_Month=Jan

I want the following Results

To_Year=R_Year+2 and To_Month=R_Month and Trn_Type='R'

I have written the following expression but it is not working

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

Pls look into my attachment and correct it expression to suit my requirement

Thanks

Re: Date in Set Expression

Sun, 16 Feb 2014 15:39:45 GMT

Hi Upali,

your set expression is correct, but incomplete. The selection of R_YEAR was transfered to the Field "To_Year", but now you need to lock the possibiliy of the selection of your base fields R_YEAR and R_Month.

In your actual expression, you limited your data modell (only datas where R_Year is 2013). In these activ datas is no information available that is associated with To_Year = 2015 (--> 2013+2). For To_Year = 2015, your field R_Year should be 2014.

You could use two ways now to get the right values:

1. Inactivate the selection of some field manually with "{<fieldname=,...>}" inside your set expression:

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

2. Inactivate the selection for every field with the pre-set "{1<...>}

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

I'm using the second expression in projects, where I'm needing fixed selection structures without the ability of an interactive analysis. Because of the transferation of values from one field to an other with a change of the value (in your case x+2), I would recommend the second one for you.

The second tricky question is about the transfer of the values of the month. If you would just like to transfer one month from R_MONTH to RO_MONTH, you could use the set expression {<[To_MONTH] = {$(=only([R_MONTH]))}>}. The restriction is, that you have to select exactly one month everytime. I would recommend the set expression {<[To_MONTH] = {$(=concat([R_MONTH],','))}>} to transfer up to every month or only some

e.g. for Jan/May/Oct becomes the set expression the following value

{<[To_MONTH] = {1,5,10}>}

But be careful with this expression and non-numeric values.

Hope this will help you.

Regards,

Chris

Re: Date in Set Expression

upaliwije — Sun, 16 Feb 2014 16:04:02 GMT

Hi Christian

Thanks a lot for your reply.But I want a little modification where R_Month=R+Month. Pls help me

Thanks

Re: Date in Set Expression

Sun, 16 Feb 2014 17:08:40 GMT

Could you specify your question? What means "R" and "Month"? Would you like to add a value on your current month?

topic Re: Date in Set Expression in QlikView

Date in Set Expression

Re: Date in Set Expression

Re: Date in Set Expression

Re: Date in Set Expression