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: 
Not applicable

New expression with aggr, can't seem to get what I want, any ideas?

Team,

Have been trying to get an expression that gives me the best of a calculation.   The expression will need to work for a variety of dimensions. 

I have tried a few things with aggr function,  but appears I may be approaching incorrect.

For example, the below gives me the Max # of Tests that Pass / Max # of Tests in Total for all the dimentions:

=    (   (max(aggr(SUM( TOTAL<Wk, PlatformName,DeviceName,TestCategory, BuildBotStep, Build> Pass),Wk, PlatformName,DeviceName,TestCategory,BuildBotStep,Build)))       /    (max(aggr(SUM( TOTAL <Wk, PlatformName,DeviceName,TestCategory,BuildBotStep,Build> [TotalTests]),Wk, PlatformName,DeviceName,TestCategory,BuildBotStep,Build)))    )

What I would rather have is a calculated Pass % calculatged inside and then take the maximum of that for each dimension.  When I do tha the expression, does not seem to work, nothing shows on chart.  Any ideas on what to do:

(max(aggr(SUM( TOTAL<Wk, PlatformName,DeviceName,TestCategory, BuildBotStep, Build> (Pass/[TotalTests])),Wk, PlatformName,DeviceName,TestCategory,BuildBotStep,Build))) 

Thanks,

D

1 Solution

Accepted Solutions
Not applicable
Author

The answer to solve this issue with aggr functions on drill in so that minimum correctly does not include dimensions that have missing categories resides in the proper placement of the if statement.  If you put the if statement inside the SUM statement, you will get 0 for min if no category exists for a dimension but if you put the SUM statement inside the if statement, those dimension with missing categories correctly show -

Example:

if(GetCurrentField(TestDrillIn)='Wk'

          ,

          // (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),Build))),  

          (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),Build))),  

                    if(GetCurrentField(TestDrillIn)='PlatformName'

                    ,

                              //(min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),PlatformName,Build))),

                              (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),PlatformName,Build))),

                                        if(GetCurrentField(TestDrillIn)='DeviceName'

                                        ,

                                                  // (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),DeviceName,Build))),

                                                  (min(aggr(   If(Build=$(vBestBuildPct),SUM(Skip)),DeviceName,Build))),

                                                    

                                                            if(  GetCurrentField(TestDrillIn)='TestCategory'

                                                            ,

                                                                       // (min(    aggr(   SUM(if(  Build=$(vBestBuildPct),Skip,100)),TestCategory, Build))) ,     

                                                                      (min(aggr(   If(Build=$(vBestBuildPct), SUM(Skip)),TestCategory, Build))),

                                                                      if(GetCurrentField(TestDrillIn)='BuildBotStep'

                                                                      ,

                                                                      //           (min(aggr(   SUM(if(Build=$(vBestBuildPct),if(Pass>=0,Skip, -1))), BuildBotStep,Build))),

                                                                      //          (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)), BuildBotStep,Build))),

                                                                      (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)), BuildBotStep,Build))),

                                                                                if(GetCurrentField(TestDrillIn)='Build'

                                                                                ,

                                                                                          //(min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),Build)))

                                                                                          (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),Build)))

          ))))))

View solution in original post

1 Reply
Not applicable
Author

The answer to solve this issue with aggr functions on drill in so that minimum correctly does not include dimensions that have missing categories resides in the proper placement of the if statement.  If you put the if statement inside the SUM statement, you will get 0 for min if no category exists for a dimension but if you put the SUM statement inside the if statement, those dimension with missing categories correctly show -

Example:

if(GetCurrentField(TestDrillIn)='Wk'

          ,

          // (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),Build))),  

          (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),Build))),  

                    if(GetCurrentField(TestDrillIn)='PlatformName'

                    ,

                              //(min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),PlatformName,Build))),

                              (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),PlatformName,Build))),

                                        if(GetCurrentField(TestDrillIn)='DeviceName'

                                        ,

                                                  // (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),DeviceName,Build))),

                                                  (min(aggr(   If(Build=$(vBestBuildPct),SUM(Skip)),DeviceName,Build))),

                                                    

                                                            if(  GetCurrentField(TestDrillIn)='TestCategory'

                                                            ,

                                                                       // (min(    aggr(   SUM(if(  Build=$(vBestBuildPct),Skip,100)),TestCategory, Build))) ,     

                                                                      (min(aggr(   If(Build=$(vBestBuildPct), SUM(Skip)),TestCategory, Build))),

                                                                      if(GetCurrentField(TestDrillIn)='BuildBotStep'

                                                                      ,

                                                                      //           (min(aggr(   SUM(if(Build=$(vBestBuildPct),if(Pass>=0,Skip, -1))), BuildBotStep,Build))),

                                                                      //          (min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)), BuildBotStep,Build))),

                                                                      (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)), BuildBotStep,Build))),

                                                                                if(GetCurrentField(TestDrillIn)='Build'

                                                                                ,

                                                                                          //(min(aggr(   SUM(if(Build=$(vBestBuildPct),Skip,100)),Build)))

                                                                                          (min(aggr(   if(Build=$(vBestBuildPct),SUM(Skip)),Build)))

          ))))))