topic Filtering on calculated dimension OR expression in QlikView

Filtering on calculated dimension OR expression

zagzebski — Mon, 14 Oct 2013 14:27:50 GMT

I hate the following chart:

Group ID	Division ID	Contract Effective Year	Revenue
1	A	2009	1,000
2	B	2009	2,000
3	C	2009	3,000
4	D	2009	4,000
5	E	2010	5,000
6	F	2010	6,000
7	G	2010	7,000
8	H	2011	8,000
9	I	2011	9,000
10	J	2011	10,000

"Contract Effective Year" is an expression. It is :

min(year([Contract_Date]))

What is the best way to select only 2010? This seems like it should be simple but I am always running into situations where I want to filter on a calculated dimension or an expression.

Re: Filtering on calculated dimension OR expression

MayilVahanan — Mon, 14 Oct 2013 14:30:59 GMT

Try like this

Bring year([Contract_Date]) as Year in script and use like this

min({<Year = {2010}>}Year)

Sum({<Year = {2010}>}Revenue)

Re: Filtering on calculated dimension OR expression

zagzebski — Mon, 14 Oct 2013 14:34:42 GMT

Thanks for the response.

Is there a way to do it without creating this field in the script? I am a front end user only of this application and don't control if/when a script is updated.

Re: Filtering on calculated dimension OR expression

MayilVahanan — Mon, 14 Oct 2013 14:37:05 GMT

Then you want to use if condition, but its take more time when compare to use set analysis,

=IF( year([Contract_Date]) = 2010, Sum(Revenue))