topic Re: Calculated dimension with range overlap in QlikView

Calculated dimension with range overlap

Mon, 24 Sep 2012 15:29:36 GMT

I have the following calculated dimension:

=If (ACSEGVAL01 >= 11110 AND ACSEGVAL01 <= 35000,'TEST',

If(

ACSEGVAL01 >= 11110 AND ACSEGVAL01 <= 12999, 'assets'

)

The problem is that the second if statement is not appearing in my chart as I don't have a line for 'assets'. What am I missing and/or don't see here?

Thanks in advance.

Re: Calculated dimension with range overlap

swuehl — Mon, 24 Sep 2012 15:33:00 GMT

Try maybe

=If ([ACSEGVAL01] >= 11110 AND ACSEGVAL01 <= 35000,

If(

ACSEGVAL01 <= 12999, 'assets','TEST'

)

Re: Calculated dimension with range overlap

Mon, 24 Sep 2012 16:37:53 GMT

I tried modifying the dimension expression as suggested but I'm having issues still... I've attached a document as an example. I would think this should be possible to do in one chart?

Re: Calculated dimension with range overlap

Mon, 24 Sep 2012 16:48:15 GMT

Attached is a better example of the issue as it contains another chart showing the problem.

Re: Calculated dimension with range overlap

danielact — Mon, 24 Sep 2012 17:07:50 GMT

The 2nd If statement will only be applied for those items which return a false to the first If statement. Since the 2nd statement is a subset of the first statement (everything in it will have returned a TRUE to the first If), nothing will ever qualify as fitting into the 2nd one.

Re: Calculated dimension with range overlap

danielact — Mon, 24 Sep 2012 17:22:21 GMT

Try creating another table in your script with the following:

LOAD * INLINE [

Field, Limit

TEST, 35000

assets, 12999

];

Then, on your table, use Field as one dimension, then the following calculated dimension:

if(ACSEGVAL01>=11110 and ACSEGVAL01<= Limit,1,null())

check supress when null, and hide the calculated dimension.

Re: Calculated dimension with range overlap

Mon, 24 Sep 2012 19:58:36 GMT

Thank you danielact! Works great...

I tried converting this into a pie chart but can't seem to hide the second dimension from appearing on the pie chart:

if(ACSEGVAL01>=11110 and ACSEGVAL01<= Limit,1,null())

Any ideas anyone? I've attached a sample.

Thanks Again!

Re: Calculated dimension with range overlap

danielact — Mon, 24 Sep 2012 20:06:16 GMT

A pie chart isn't the thing to use when you have ovelap.

If you want to do it anyway, you'd have to take out the second dimension and use an if statement in the expression:

sum(if(ACSEGVAL01>=11110 and ACSEGVAL01<= Limit,ACSEGVAL01,0))