you may need to do a sum( if() ) rather than an if( sum()) to get the details to aggregate correctly
try changing this:
sum ( If( Num(Floor(EventDate))<Num(Floor(Now())) , [NoShows],0) )
sum ( If( Num(Floor(EventDate))<Num(Floor(Now())) , [Registrants],1) )
ps: the syntax may not be perfect. If you share your qvw i can be precise.
Thanks, replacing now() with today() seems to have resolve the thing with the object trying to recompute (and scroll back to the left) every time I clicked on it.
Hi Everyone else, I went with a textbox hack to hold the formulas I will copy/paste to the three dropoff % cells on the totals row in excel for the respective reports. (I'm already making it wrap header text because it loses that bit of formatting when I export to excel even though I have headers wrapped in the qvw.)
Drop off % to put on drop off totals line of screeningflureport.
basically no shows (who registered) divided by no shows (who registered) + registered shows which simulates no shows divided by registered. (The total in the registered column includes all past and future events, but drop off and no shows need to be only past events, so I looked at who showed and didn't show whose totals only show past events.)
Brutal part of this is combining registration and attendance activity in one table, so I'm pretty sure this hack or a series of resident loads filtered to past events and expressions in script are the kind of solution needed. I tried resident load stuff in the script, but something in the data structure was eluding me, so not sure I could communicate it perfectly here. It's one of those where I'm happy with the number in excel, and the numbers on the event-specific rows, and just needed more flexible to define what I want on the total row. Maybe qlikview will have the option for us to write whatever expression we want there some day- Avg, Sum, <Expression> like that.
Thanks to all for the help. I tried everything in here, and pitched a range sum and some stuff in script on top, but textbox/excel hack is a bearable compromise for me for now.