11 Replies Latest reply: Nov 10, 2016 10:42 AM by Cassandra Baqir RSS

    Dual Class Syntax

    Cassandra Baqir

      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

          What error are you getting?

            • Re: Dual Class Syntax
              Cassandra Baqir

              Error in calculated dimension

                • Re: Dual Class Syntax
                  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

                  • Re: Dual Class Syntax
                    John Witherspoon

                    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
                        Cassandra Baqir

                        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

                          • Re: Dual Class Syntax
                            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

                              • Re: Dual Class Syntax
                                Cassandra Baqir

                                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

                                • Re: Dual Class Syntax
                                  Cassandra Baqir

                                  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
                                      Cassandra Baqir

                                      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

                                        • Re: Dual Class Syntax
                                          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

                                            • Re: Dual Class Syntax
                                              Cassandra Baqir

                                              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.