Calculated dimension with range overlap

[#1]

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.

[swuehl]

Try maybe

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

   If(

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

     )

)

[#3]

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?

[#4]

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

[danielact]

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.

[danielact · Accepted solution]

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.

[#7]

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!

[danielact]

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))