topic Re: Dual Class Syntax in QlikView

Dual Class Syntax

cbaqir — Wed, 09 Nov 2016 16:57:13 GMT

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

Re: Dual Class Syntax

sunny_talwar — Wed, 09 Nov 2016 17:00:46 GMT

What error are you getting?

Re: Dual Class Syntax

cbaqir — Wed, 09 Nov 2016 17:01:22 GMT

Error in calculated dimension

Re: Dual Class Syntax

sunny_talwar — Wed, 09 Nov 2016 17:08:05 GMT

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

Re: Dual Class Syntax

johnw — Wed, 09 Nov 2016 17:21:36 GMT

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.

Re: Dual Class Syntax

cbaqir — Wed, 09 Nov 2016 19:18:06 GMT

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,

Re: Dual Class Syntax

sunny_talwar — Wed, 09 Nov 2016 19:29:04 GMT

Does this look better?

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

Re: Dual Class Syntax

cbaqir — Wed, 09 Nov 2016 19:36:26 GMT

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.

Re: Dual Class Syntax

cbaqir — Wed, 09 Nov 2016 20:28:08 GMT

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.

Re: Dual Class Syntax

cbaqir — Wed, 09 Nov 2016 20:41:55 GMT

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)

Re: Dual Class Syntax

sunny_talwar — Thu, 10 Nov 2016 02:00:34 GMT

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

Re: Dual Class Syntax

cbaqir — Thu, 10 Nov 2016 15:42:39 GMT

The logic I am trying to get to is:

Single defect = the total days in that GPQ across all occurrences;

Multiple defects = the avg of the of days in GPQ across all defects

avg(aggr(sum({$}GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE))

For example, if you look at DEFECT_ID 3004 only, the TaT should be the sum of time in one step - so for Regional Clinical Informatics that is 129 + 169 = 298.

If you look at multiple defects, say 3004 and 2967, it should be 208 (298 +117/ 2 distinct DEFECT_ID).

That being said, I think that it is working now if I use avg(aggr(sum({$}GOV_PROCESS_QUEUE_TAT), DEFECT_ID, GOV_PROCESS_QUEUE)) as the expression except for the label in your attachment.