Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

10 Replies
Not applicable
Author

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

Not applicable
Author

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?

Miguel_Angel_Baeyens

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

Not applicable
Author

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.

Miguel_Angel_Baeyens

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

Not applicable
Author

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

Miguel_Angel_Baeyens

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

Not applicable
Author

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

thank you Migueal for your time and your good replies

llauses243
Creator III
Creator III

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