week1 | |||||||
---|---|---|---|---|---|---|---|
c | b | c | s | d | f | g | |
OTD % - offers average | 0.0% | 11.1% | 75.0% | 0.0% | 65.6% | 0.0% | 100.0% |
OTD % - partners average | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% | 0.0% |
otd_num | 0 | 2 | 24 | 0 | 227 | 0 | 1 |
otp denom | 56 | 18 | 32 | 3 | 346 | 17 | 1 |
Right now, i can’t get the right formula for [OTD % - partners average] , please help.
The logic for calculation is sum(otd* OTD % - offers average)/ counta( vendor alias)
ex: OTD % - partners average =0*56 +11.1%*18+75% * 32+0 * 3+65.6% * 346+ 0 * 17+ 100% *1= 36.28%
otd_num formula: distinct_count(ifelse({otd_status}=1,{Offer Published},NULL))
otp denom formula :distinct_count({Offer Published})
OTD % - offers average= otd_num/otp_donom