Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Dual Class Syntax

I am getting an error in my class buckets. What is wrong here?

=Dual(If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID) < 60), '<60',
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID) > 120), '>120',
Replace(Class(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT),DEFECT_ID)), 60), '<= x <', '-'))),
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID) < 60), 0,
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID) > 120), 120,
Class(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT),DEFECT_ID)), 60))))

1 Solution

Accepted Solutions
sunny_talwar

Are you 100% confident that your 98 day number is correct? Can you check if it could be 80 days for Regional Clinical Informatics

Capture.PNG

View solution in original post

11 Replies
sunny_talwar

What error are you getting?

cbaqir
Specialist II
Specialist II
Author

Error in calculated dimension

sunny_talwar

Almost feel like you probably would need another Aggr() outside, but not sure on what level (Dimension/Dimensions) are you checking this avg on?

Avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID) < 60

johnw
Champion III
Champion III

I agree with Sunny. It's a valid expression, but only returns a single value. I'm guessing you want this in the context of some other dimension. Something like:

=aggr(Dual(If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, Dimension) < 60), '<60',
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, Dimension) > 120), '>120',
Replace(Class(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT),DEFECT_ID, Dimension)), 60), '<= x <', '-'))),
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, Dimension) < 60), 0,
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, Dimension) > 120), 120,
Class(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT),DEFECT_ID, Dimension)), 60)))), Dimension)

But I'm unclear why this is a dimension at all, instead of an expression, though again, you'd need the Dimension in the expression too.

I also suspect there's an easier way to do whatever you're trying to do here.

cbaqir
Specialist II
Specialist II
Author

So this seemed to be right at first:

=aggr(Dual(If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) < 60), '<60',
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) > 120), '>120',
Replace(Class(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT),DEFECT_ID, GOV_PROCESS_QUEUE)), 60), '<= x <', '-'))),
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) < 60), 0,
If(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) > 120), 120,
Class(avg(Aggr(Sum(GOV_PROCESS_QUEUE_TAT),DEFECT_ID, GOV_PROCESS_QUEUE)), 60)))), GOV_PROCESS_QUEUE)

But then I realized that everything is landing in the <60 bucket and they shouldn't be. Example attached.

For example,

11-9-2016 12-17-18 PM.jpg

sunny_talwar

Does this look better?

Capture.PNG

Not sure what are you expecting to see, may be provide some context if this doesn't work

cbaqir
Specialist II
Specialist II
Author

No, because if you select on Governance Process Queue, like Regional Clinical Informatics, you now see the avgs across 3 buckets when it should be 1 for 98 days.

11-9-2016 12-35-57 PM.jpg

cbaqir
Specialist II
Specialist II
Author

This is the view I was trying to fix originally.

For one GOV_PROCESS_QUEUE, I would expect to see one average but in the right bucket.

cbaqir
Specialist II
Specialist II
Author

I thought it might need a "total" but then it's still not in the right bucket.

=aggr(Dual(If(avg (Aggr (Sum( total GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) < 60), '<60',
If(avg(Aggr(Sum(total GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) > 120), '>120',
Replace(Class(avg(Aggr(Sum(total GOV_PROCESS_QUEUE_TAT),DEFECT_ID, GOV_PROCESS_QUEUE)), 60), '<= x <', '-'))),
If(avg(Aggr(Sum(total GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) < 60), 0,
If(avg(Aggr(Sum(total GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE) > 120), 120,
Class(avg(Aggr(Sum(total GOV_PROCESS_QUEUE_TAT),DEFECT_ID, GOV_PROCESS_QUEUE)), 60)))), DEFECT_ID)

11-9-2016 1-41-33 PM.jpg