Dual Class Syntax

[cbaqir]

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

[sunny_talwar]

What error are you getting?

[cbaqir]

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]

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]

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,

[sunny_talwar]

Does this look better?

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

[cbaqir]

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.

[cbaqir]

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]

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)