10 Replies Latest reply: Jul 25, 2011 9:31 AM by jmowla70

# very slow performance of if() expression!

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

• ###### very slow performance of if() expression!

Use Set Analysis instead of If(), it will drastically improve the performance.

Change your eexpression with Set Analysis. If you have any trouble with syntax, let me know. I can help.

Thanks

Chaitanya

• ###### very slow performance of if() expression!

thanks Chaitanya,

i tried that by Set Analysis; but i got two different answers!

=count({\$<DeliverDate={"<=\$(Cal_DateID)"},ChequeDate={">\$(Cal_DateID)"}>}SalesID) RETURNS 5

=count(if(DeliverDate<=Cal_DateID and ChequeDate>Cal_DateID, 1)) RETURNS 2

what's wrong?

• ###### Re: very slow performance of if() expression!

Hi,

Probably something in the syntax, and some differences in the date format for all fields, that must be exactly the same, so the comparison makes sense

```Count({< DeliverDate = {"<=\$(=Date(Cal_DateID))"}, ChequeDate = {">\$(=Date(Cal_DateID))"} >} SalesID)
```

That's why I'm using the Date() function above.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### very slow performance of if() expression!

Miguel,

all "DeliverDate", "ChequeDate" and "Cal_DateID" are in number format, then no worry about date format.

"DeliverDate" and "ChequeDate" are in Sales table; and "Cal_DateID" is in Calendar table

no relation between these two tables exist.

when i put a single value like '40747' in a variable, the set analysis works fine agains that; but in the real world "Cal_DateID" is an array of values (eg: users selects 30 days from Calendar)

for a single selection, if i put the value in a variable (like vCalDateId) it works fine.

• ###### Re: very slow performance of if() expression!

Hi,

If both tables are not linked, then that's the answer. What I do is to create two variables populated by means of a calendar / slider object, and use them in the set analysis, so it always taes the correct values.

Take a look at this application that uses variables, set analysis and calendars.

Hope this helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### very slow performance of if() expression!

hi Migueal,

i saw the sample.

the Set Analysis works find if we want to use single value! for example =count({\$<DeliverDate={"<=\$(=Cal_DateID)"},ChequeDate={">\$(=Cal_DateID)"}>}SalesID) will exapand to =count({\$<DeliverDate={"<=40289"},ChequeDate={">40289"}>}SalesID) and this is very equalant of =count(if(DeliverDate<=40289 and ChequeDate>40289,SalesID))

my actual criteria based on if (DeliverDate<=Cal_DateID and Cal_DateID<ChequeDate) and i need to change that to Set Analysis!

the important thing is Cal_DateID is not a single value and it is a data island. it is ok if i wanted to check equality by expanding Cal_DateID by using concat().

the problem is i need to check a range "DeliverDate <= Cal_DateID < ChequeDate" while Cal_DateID is an array. it means that i need to repeat each rows of Sales data that meet the above criterion by number of Cal_DateIDs that meets the criterion!

if i expand it by concat(), when user selected two different date it looks like "{\$<DeliverDate={"<=40289","<=40290"},ChequeDate={">40289",">40290"}>}SalesID"

i think the problem is here and it could not act as equalanet if() statement

OMG i'm really confused.

new to Set Analysis, english as a secondary language

• ###### Re: very slow performance of if() expression!

Hi,

Depending on your data model and how you interact with the objects in your document, the If() may be the only solution. Note that Set Analysis is calculated once for the whole chart (check here for further information on the subject among other threads), and not row by row (value by value). So in some cases, you do need to use the If() instead of Set Analysis.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: very slow performance of if() expression!

this is a very bad news to me

in my sample qvw file the Sales table contains 50 rows! and Calendar 480 rows

the simple "=count(if(DeliverDate<=Cal_DateID and ChequeDate>Cal_DateID, 1))" returns me 5,666 rows.

i don't have any imagination that what will happen when i populate table with 1M or more records.

the if() statement is very slow and i hope better performance by using set analysis

• ###### Re: very slow performance of if() expression!

Hi,

This is my offer, pls see image adjust

1.-  Answer to why slow CPU

2.-  Why not to asociate ChequeDate & DeliveDate  vía Calendar ?

Goos luck, Luis

• ###### Re: very slow performance of if() expression!

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?