Total percent value for If EventDate<Now(), Total / Total

[stevelord]

Hi, I have an expression that works nicely on the line-by-line stuff, but struggling to get the total to populate correctly.

The expression is looking for no shows divided by registrants, but only where the event date is less than today.  The answer is a registration drop off rate.  The percents on the event lines are fine, but having trouble with the percent on the total line.

Straight table:

Dimensions:

EventID

EventDate

Registrants

Attendees

No-Shows

Expression:

If(Num(Floor(EventDate))<Num(Floor(Now())), SUM(No-Shows)/SUM(Registrants),0)

The result is a percentage on the lines where an event has taken place, and a 0 on lines with future events.  Depending on my selections, I get 0%, a fraction of 1% or some arbitrary average of the percentages of event drop-offs not factoring in the overall # of attendees and no shows.

Is there a particular option in the totals that can help, or a way for me to write a separate expression for the value that goes in the totals row of this expression's column?

Thanks!

PS> There's another interesting phenomenon where if I put the expression in the table, the Now() causes it to try to recompute every time I click, but thinking I can move expression to script to solve that  Still not sure how to move expression to script that can produce both the aggregate and event-specific drop off % in the one column though.

[JonnyPoole]

Hi -

you may need to do a sum( if() )  rather than an if( sum()) to get the details to aggregate correctly

try changing this:

If(Num(Floor(EventDate))<Num(Floor(Now())), SUM(No-Shows)/SUM(Registrants),0)

to:

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.

[#3]

Hi Steve,

Instead of using Now(), you can use Today() function. It just gives you date part.

And if possible, can you share sample data, few row in excel.

Regards,

Kalpesh

[stevelord]

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.

Total Dropoff

=Report!V2/(Report!V2+Report!S2)

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.