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

    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!
          Chaitanya Kadiyala

          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!
                  Miguel Angel Baeyens de Arce

                  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!
                          Miguel Angel Baeyens de Arce

                          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!
                                  Miguel Angel Baeyens de Arce

                                  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

                                       

                                      thank you Migueal for your time and your good replies

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

                                          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.

                                              delivery.pngsample 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?