i want to count the number of prodct and market (note the actual table also have area and category as dimensions, so we need to use aggr function to roll up the qty) where accuracy rate is low and volume is high by category
this is the main table, assume there is only one line for A to keep things simple
product market target_qty(K) actual_qty(K) abs_error area category
A us 0 7 7 north kids
accuracy is 1-abs_error/target_qty, accuracy should be 0, but since target qty is 0, it gives us null for accuracy rate.
when i do accuracy rate is lower than .5, a null value is not being considered.
this is what i wrote:
count(distinct if(aggr(1-sum(abs_error)/(targetl_qty) < 0.5 and actual_qty>1, product & market))
the count is 0? how do i handle the null calculated value?
