9 Replies Latest reply: Aug 26, 2015 4:15 PM by Steve Lord RSS

    Straight table Percentage on the Total line not correct

    Steve Lord

      Hi, Below is an example of how my straight table's total row is behaving.  The expression for % Drop Off is just:

      [Total No-Shows] / ([Total Registered and Screened] + [Total No-Shows])

      In below example, the expected result is 9.09% on the totals row but the % is coming out above that.  It seems like the total row is just doing [Total No-Shows] / [Total Registered and Screened] (or maybe some other rounding anomaly is making it look like that).  The % on the other rows is the expected result for those rows.  Please advise.  Thanks!

           

      EventIdTotal Registered and ScreenedTotal No-Shows% Drop off
      2402410.00%<-something wrong on the total
      a8089.09%
      b6069.09%
      c100109.09%

       

      My present workaround is to paste an expression into cells like that expression total % on the excel export which is a barrier to me handing the task off for self service.

        • Re: Straight table Percentage on the Total line not correct
          Sinan Ozdemir

          Hi Steve,

           

          In the Total Mode of expression, choose Average of Rows:

          Capture.PNG

          Hope this helps.

          • Re: Straight table Percentage on the Total line not correct
            Steve Lord

            Edit: I actually get a null when I set the total to Expression totals.  I had an average as a placeholder for the percent in the interim. (Can't use the average in reality because the groups are all different sizes and the average of their percentages is an inaccurate % for the totals row.)

              • Re: Straight table Percentage on the Total line not correct
                Sinan Ozdemir

                I don't know your expression, but you may need to add Sum() in your expression:

                Capture.PNG

                  • Re: Straight table Percentage on the Total line not correct
                    Steve Lord

                    Hi Sinan, I've tried sum() rangesum() sum(aggr()) and the like and all give either a null or a 0.00% on the totals row when I set it to expression total, though all give expected results on the other rows.  The average of rows was giving a percent that's off because it's not considering how big the groups are on the various rows.

                     

                    Presently I am working on a hack in script where I do a concatenate load 'Totals' as EventId, then do the sums and other expressions Resident Tablename group by 'Totals';.  Will see how that works too- since this table doesn't need to be filtered by anything on the front end...  (People feel free to keep offering ideas in case my hack fails too. )

                     

                    Might be nice functionality for qlikview to let us enter a function for the total of an expression in case the built-in logic gets stuck on something like this.  Or it could be a bug... or maybe just a more optimal expression syntax I'm missing...

                      • Re: Straight table Percentage on the Total line not correct
                        Sinan Ozdemir

                        Hi Steve,

                         

                        With the provided data depiction, I don't get any null or 0.00% values. Can you post a more comprehensive dataset which represents the complexity of your problem?

                         

                        I don't think you even need to hit the load script at all..

                          • Re: Straight table Percentage on the Total line not correct
                            Steve Lord

                            It's okay, I blew the problem to smithereens with my hack now.  Concatenate load 'Totals' as EventId, sum all of the fields as fieldname, resident tablename, group by 'Totals'.  And all of my expressions back on the straight table are unphased- except I now have a correct % value for the totals row created by my hack that is replacing the other totals row.

                             

                            Since that % computation was working okay on the individual events, my hack was to create a fake event called Totals and let my expression work on THAT too.  Just need to turn off two dozen totals now... And the null or 0 may have been happening in the total of that % computation if one of the rows had a null or 0 involved in the denominator or maybe the numerator and denominator - the array of data is huge and quite possible for a 'fly in the ointment' to have messed it all up.  I don't know if maybe some variation of if() statement would've helped, but the script hack seems to have done the job. (Fired a cannon at a mosquito and hit only the mosquito. )

                             

                            Anyway, here's my augmented script - the couple of fields I needed for my % expression are in there (registrations total and no-shows total):

                            expression that gave a correct total percentage afterwards was the noshows/(noshows+totalregistrations) bit.

                            ACMEScreenEventRpt:

                            LOAD EventId,

                                 Date(EventDate) as EventDate,

                                 Address1,

                                 SubLocation,

                                 [Total Appointment Capacity],

                                 [Open Time Slots],

                                 [Registrations Total],

                                 [Registered (Emp)],

                                 [Registered (Dep)],

                                 Reservations,

                                 [Coaching Registration Total],

                                 [Coaching Registration (Emp)],

                                 [Coaching Registration (Dep)],

                                 [Check-Ins Total],

                                 [Check-Ins (Emp)],

                                 [Check-Ins (Dep)],

                                 [No-Shows Total],

                                 [No-Shows (Emp)],

                                 [No-Shows (Dep)],

                                 [Registered Attendance Total],

                                 [Registered Attendance (Emp)],

                                 [Registered Attendance (Dep)],

                                 [Walk-Ins Total],

                                 [Walk-Ins (Emp)],

                                 [Walk-Ins (Dep)],

                                 'ScreeningReport' as ReportType

                            FROM

                            C:\ProgramData\QlikTech\Documents\Data\ACMEScreenEventRpt.qvd

                            (qvd);

                             

                            concatenate load 'Totals' as EventId,

                                 'ScreeningReport' as ReportType,

                                 date('8/10/2014') as EventDate, //just to make it the first record on the list - events began 8/11/2014 in this test area.

                            sum([Total Appointment Capacity]) as [Total Appointment Capacity],

                            sum([Open Time Slots]) as [Open Time Slots],

                            sum([Registrations Total]) as [Registrations Total],

                            sum([Registered (Emp)]) as [Registered (Emp)],

                            sum([Registered (Dep)]) as [Registered (Dep)],

                            sum(Reservations) as Reservations,

                            sum([Coaching Registration Total]) as [Coaching Registration Total],

                            sum([Coaching Registration (Emp)]) as [Coaching Registration (Emp)],

                            sum([Coaching Registration (Dep)]) as [Coaching Registration (Dep)],

                            sum([Check-Ins Total]) as [Check-Ins Total],

                            sum([Check-Ins (Emp)]) as [Check-Ins (Emp)],

                            sum([Check-Ins (Dep)]) as [Check-Ins (Dep)],

                            sum([No-Shows Total]) as [No-Shows Total],

                            sum([No-Shows (Emp)]) as [No-Shows (Emp)],

                            sum([No-Shows (Dep)]) as [No-Shows (Dep)],

                            sum([Registered Attendance Total]) as [Registered Attendance Total],

                            sum([Registered Attendance (Emp)]) as [Registered Attendance (Emp)],

                            sum([Registered Attendance (Dep)]) as [Registered Attendance (Dep)],

                            sum([Walk-Ins Total]) as [Walk-Ins Total],

                            sum([Walk-Ins (Emp)]) as [Walk-Ins (Emp)],

                            sum([Walk-Ins (Dep)]) as [Walk-Ins (Dep)]

                                 RESIDENT ACMEScreenEventRpt

                                 group by 'Totals', 'ScreeningReport', date('8/10/2014');

                            • Re: Straight table Percentage on the Total line not correct
                              Steve Lord

                              And I agree I shouldn't have had to hit the load script.  My expression gave perfect answers on all of the rows except the totals row.  And it was the only expression out of 20+ expressions with that problem.  The script hack was a way to step back from whatever the table object was trying to do, and do it myself.  So now the table object doesn't have to grapple with a totals row.