I have the following table:
t:([]date:.z.d+neg til 100; region:100#(`us`eu`asia`au); product:100#(`car`insurance`loan`other); qty:100?1f);
date region product qty
---------------------------------------
2019.11.28 us car 0.3998898
2019.11.27 eu insurance 0.592308
2019.11.26 asia loan 0.9796166
...
What I want is to check if the qty has been above a given threshold, say 0.5, for more than n days, say 3 days. Here days can actually just be the number of consecutive observations.
I want to check this for each of the 4 products I have, and by region.
I thought about applying a rolling function over n days, and apply the following function:
myfunc:{[l]
:all l>0.5;
};
where 0.5 is my threshold, but it is not really working...
One approach using fby:
q)consec:{s in(s:sums[differ y])where(x-1){x&prev x}/y};
q)`region`product`date xdesc select from t where (consec[3];0.5<qty)fby([]region;product)
date region product qty
-------------------------------------
2019.11.04 us car 0.949975
2019.10.31 us car 0.8481567
2019.10.27 us car 0.9367503
2019.09.28 eu insurance 0.6884756
2019.09.24 eu insurance 0.9598964
2019.09.20 eu insurance 0.6789082
2019.09.16 eu insurance 0.726781
2019.09.12 eu insurance 0.5830262
2019.09.08 eu insurance 0.7750292
2019.11.21 au other 0.5347096
2019.11.17 au other 0.5785203
2019.11.13 au other 0.6137452
2019.11.09 au other 0.6919531
2019.09.30 au other 0.7278528
2019.09.26 au other 0.7520102
2019.09.22 au other 0.6430982
2019.09.18 au other 0.9877844
2019.09.14 au other 0.8355065
2019.09.10 au other 0.9149882
2019.09.06 au other 0.6324114
/to make it a sequence of 5 consecutive
`region`product`date xdesc select from t where (consec[5];0.5<qty)fby([]region;product)
/to make the threshold >0.6
`region`product`date xdesc select from t where (consec[5];0.6<qty)fby([]region;product)