Straight table Percentage on the Total line not correct

[stevelord]

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!

EventId	Total Registered and Screened	Total No-Shows	% Drop off
	240	24	10.00%	<-something wrong on the total
a	80	8	9.09%
b	60	6	9.09%
c	100	10	9.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.

[sinanozdemir]

Hi Steve,

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

Hope this helps.

[stevelord]

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

[sinanozdemir]

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

[stevelord]

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

[sinanozdemir]

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

[stevelord · Accepted solution]

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

[stevelord]

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.

[stevelord]

by table I meant straight table..  Though now I suppose I could just use a table box...

[stevelord]

Thanks for taking time to offer ideas Sinan.  I think this is one I chalk up as having it solved though the root cause of the issue remains a mystery. (Have had a few of those over the years. (