Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Date in Set Expression

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

3 Replies
Not applicable

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


upaliwije
Creator II
Creator II
Author

Hi Christian

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

Thanks

Not applicable

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