very slow performance of if() expression!

[#1]

i need to compute average number of days between product's delivery date and customer's payment cheque!

because of that, no relation exists between Calendar and Sales tables.

average computation base on if() statement and is very slow. [=avg(if(Cal_DateID >= DeliverDate and Cal_DateID < ChequeDate,Cal_DateID - DeliverDate))]

any sugesstion to get that more fast?

the current rows of sales in the attached sample is just 5000 rows and in production we may have milions of rows

[#2]

thanks Luis,

i looked at your code very carefully.

i can not relates Sales and Calendar tables and they are really data islands. if i provide a relation between them the qlikview will filter the Sales table with stright relation (eg: in your code it will filtered by appropriate ChequeDate) but i need to filter Sales records that meet this criteria "DeliverDate<=Cal_DateID and Cal_DateID<ChequeDate". it means Sales may repeat by number of Cal_DateID that meets the criteria!

i just reduced my data to 20 rows and prepare a visual sample data when selection changes.

sample data is on the right hand side, there is two rows (6,13) exist with ChequeDate : 2010/1018

then when i select 2010/10/17 from Calendar, just two rows will show on table

when i select 2010/10/16 from Calendar too, then four rows will meet the criteria! and so on.

if i bind two tables by relation this will never happen, because qlikview will filter Sales very stright. in this way the above criteria does not mean anything!

does qlikview support this type of if() by Set Analysis? or do we able to implement every if() statements with Set Analysis?