Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Dual Replace Class Labels

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:

3-3-2016 7-44-23 AM.jpg

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.

1 Solution

Accepted Solutions
sunny_talwar

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


Capture.PNG

View solution in original post

10 Replies
Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

Can you possibly attached sample file?

This help

cbaqir
Specialist II
Specialist II
Author

Added to original post.

sunny_talwar

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


Capture.PNG

cbaqir
Specialist II
Specialist II
Author

Perfect! Thanks, Sunny!

MK_QSL
MVP
MVP

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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