Dual Replace Class Labels

[cbaqir]

My current dimension is:

=Dual(If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) <= 120, Class(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID), 60), '>120'),
If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) <= 120, Class(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID), 60), 120))

and it gives me these labels:

My customer wants the labels to display (and then sort the same way) more simply:

<60

60-120

>120

I've been playing with the Dual Replace function in the dimension but can't get it to work correctly.

Thanks.

[Gabriel]

Hi,

Can you possibly attached sample file?

This help

[cbaqir]

Added to original post.

[sunny_talwar · Accepted solution]

May be this:

=Dual(

     If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) < 60, '<60',

     If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) > 120, '>120',

     Replace(Class(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION),REQUEST_ID), 60), '<= x <', '-'))),

     If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) < 60, 0,

     If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) > 120, 120,

     Class(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION),REQUEST_ID), 60))))

[cbaqir]

Perfect! Thanks, Sunny!

[MK_QSL]

You can use below one also

=Aggr(

  If(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION)< 60, Dual('<60',1),

  If(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION)>= 60 and Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION) <= 120, Dual('60-120',2),

  If(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION)> 120, Dual('>120',3))))

  ,REQUEST_ID)

[cbaqir]

So to apply the same logic to this version:

=Dual(
If(WORKFLOW_DURATION <= 120,
Class(WORKFLOW_DURATION, 60), '>120'), 
If(WORKFLOW_DURATION <= 120,
Class(WORKFLOW_DURATION, 60), 120))

would it be something like this:

=Dual(
If(WORKFLOW_DURATION) < 60, '<60',
If(WORKFLOW_DURATION) > 120, '>120',
Replace(Class(WORKFLOW_DURATION)), 60), '<= x <', '-'),
If(WORKFLOW_DURATION) < 60, 0,
If(WORKFLOW_DURATION) > 120, 120,
Class(WORKFLOW_DURATION), 60)

[sunny_talwar]

Some missing parenthesis:

=Dual(
If(WORKFLOW_DURATION) < 60, '<60',
If(WORKFLOW_DURATION) > 120, '>120',
Replace(Class(WORKFLOW_DURATION)), 60), '<= x <', '-'))),
If(WORKFLOW_DURATION) < 60, 0,
If(WORKFLOW_DURATION) > 120, 120,
Class(WORKFLOW_DURATION), 60))))

[cbaqir]

That's giving me "Error in calculated dimension"

[sunny_talwar]

And you had some extra parenthesis elsewhere:

=Dual(

If(WORKFLOW_DURATION < 60, '<60',

If(WORKFLOW_DURATION > 120, '>120',

Replace(Class(WORKFLOW_DURATION, 60), '<= x <', '-'))),

If(WORKFLOW_DURATION < 60, 0,

If(WORKFLOW_DURATION > 120, 120,

Class(WORKFLOW_DURATION, 60))))