topic Limit values baesd on range in dimension in QlikView

Limit values baesd on range in dimension

Thu, 26 Apr 2012 15:13:10 GMT

Hi ,

I am trying to limit the dimension based on range.My dimension is month year.I want to limit the values between Jan 2011 and sep 2011.I tried like this

If (monthyear>='Jan 2011' and monthyear<='sep 2011',monthyear)

Its not working.

Thanks

Limit values baesd on range in dimension

chematos — Thu, 26 Apr 2012 19:00:08 GMT

Hi,

do you have a field [year] and a field [month]??

Try this if is the case and let me know.

If (year>=2011 and Month(month)>=1 and Year<=2011 and Month(Month)<=9,monthyear)

Regards,

Chema

Limit values baesd on range in dimension

Thu, 26 Apr 2012 19:16:47 GMT

Hi Chema,

Thank you for the reply.I tried with the expression you metioned.It's not working.I am using a bar chart.I am using the monthyear as dimesion and following as expression.

(sum(if(adnum=6, 1, 0))/count(adminId))/(sum( if(stnum=5,Expect,0))/count(adminId))

I tried limiting the expression based on monthyear field.It did not work .Thats why I am trying limiting the values in dimension.Is there a way I can limit the values in the expression based on the dimension?.I tried set analysis in the expression, but failed.May be I am doing in a wrong way.Any suggestions ?I have to limit the values between Jany 2011 and Sep 2011

Thank you

Limit values baesd on range in dimension

chematos — Thu, 26 Apr 2012 19:55:42 GMT

Hi,

So you don´t have Year and Month as fields??? How do you built the field monthyear?

I´ve written my previous code like a dimension and it´s working well for me.

Could you attached your file?

If this helps you, if you make Num(monthyear), that returns a number so you could calculate those numbers and in the expression write something like:

sum({<monthyear={">=40909"},monthyear={"<=40999"}>}WHATEVER)

In this example 40909 is Jan 2012 and 40999 is 1 less Apr 2012

Regards,

Chema

Limit values baesd on range in dimension

msteedle — Fri, 27 Apr 2012 00:19:47 GMT

You are generally on the right track, but you need to make sure that monthyear is a date or numeric field as opposed to a string if you want to use greater than/less than operators.

If you try Jose's most recent approach, the formatting will have to be the same between the monthyear field and the values you plug into the set analysis expression (e.g. 40909 in his example).