topic Re: Re: Set Expression Date in QlikView

Set Expression Date

upaliwije — Mon, 17 Feb 2014 07:55:48 GMT

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

Pls refer my attachment and I want the above highlighted part of the expression to change as R_Month should be months coming after the R_Month and all other conditions should remain as it is. Here I have defined R_Month > Max(R_Month) But I want it to be changed R_Month<>Max(R-Month)

Eg If User select R_Month=Jan Expression should select Feb-dec

R_Month=Dec Expression should select Jan-Nov etc

R_Month=Jun Expression should select Jul-May etc

Pls help me

000001.qvw

Re: Set Expression Date

Mon, 17 Feb 2014 08:03:14 GMT

try this

Date = {'>=$(=MonthStart(Max(Date), -3))<=$(=MonthEnd(Max(Date)-1

Re: Set Expression Date

upaliwije — Mon, 17 Feb 2014 08:22:25 GMT

Thanks

I can not understand it . pls explain

Re: Set Expression Date

Mon, 17 Feb 2014 08:37:17 GMT

The query is a bit unclear but from what I understood, you want to exclude the selected month. You can do this by creating a variable and using that in your set expression.

Define a variable "xMonths" as "=Replace(concat({<R_MONTH=>} DISTINCT R_MONTH, ',') ,(Concat(DISTINCT R_MONTH, ',')&','),'')"

This variable will give you all month names except the one you have selected.

Then use this variable in your set expression for calculations like below

R_MONTH={$(xMonths)}

Thanks,

Sajeevan

Re: Set Expression Date

upaliwije — Mon, 17 Feb 2014 08:42:35 GMT

Hi Sajeewan

Thanks

Can you please rewrite my expression having inserted the variable created as per your advice

Re: Re: Set Expression Date

Mon, 17 Feb 2014 09:10:55 GMT

Please refer the attached file. I have created a chart object for demonstrating how the variable can be used. Please also refer the variable from variable overview.

Re: Set Expression Date

upaliwije — Mon, 17 Feb 2014 09:40:33 GMT

Thanks for you reply and attachment. But as I mentioned in my original question other conditions too should be in tact in y expression

To_YEAR={$(=max(R_YEAR)+1)},To_MONTH={'>=$(=max(R_MONTH))<=$(=max(R_MONTH))'},

Please also consider those conditions and modify my expression

Thanks a lot for your help