5 Replies Latest reply: Sep 23, 2014 3:25 AM by Roya Mahdavi RSS

    Summing multiple if statements

      Hi,

       

      We're using this expression =if(IsNull(TimeStamp(Max({$<QVD_Id={10}>}Updated))), 0, 1) to check if a report has been updated in a given time interval. We'd like to plot the values in a line chart. This expression only checks one report, namely where the QVD ID is equal to 10. We have 10 other reports with QVD IDs ranging from 2 to 20 and we'd like to summarize all the results in the if statements.

       

      Is there any easier way to do this than to do the calculations below

       

      (=if(IsNull(TimeStamp(Max({$<QVD_Id={10}>}Updated))), 0, 1)) + =if(IsNull(TimeStamp(Max({$<QVD_Id={2}>}Updated))), 0, 1) + ... =if(IsNull(TimeStamp(Max({$<QVD_Id={20}>}Updated))), 0, 1)

       

      Hope you guys can help. Appreciate it!! =)

        • Re: Summing multiple if statements
          Gysbert Wassenaar

          Something like this perhaps:

           

               count({<Update={'>=$(vTimeIntervalStart)<=$(vTimeIntervalEnd)'}>} distinct QVD_Id)

           

          vTimeIntervalStart and vTimeIntervalEnd would be two variables to enter the start and end of the time interval. Then it's simply a matter of counting the distinct number of QVD_Id's that have an Update time value between that start and end time.

            • Re: Summing multiple if statements

              Thank you for the quick reply.

               

              It should have been mentioned in the question, however our report sheet contains a calendar and time list box so that users can select their time interval. We also have more QVD IDs (more than 10) but will only be using specific QVD IDs, not all. As an example, we have a list of QVD_IDs:list1={ 2, 10,11,14,15,20} that we are interested in, but the complete list of QVD_IDs contains more values. We already know that the expression =if(IsNull(TimeStamp(Max({$<QVD_Id={10}>}Updated))), 0, 1) returns 0 if the report (with QVD_ID=10) has not been updated in the chosen time interval, and returns 1 if the report has been updated. If it is possible, we therefore want to use this statement but make a sum of the the results for all the reports in list1, yielding ex. 5 if five of the reports successfully uploaded in the specified time interval.

                • Re: Summing multiple if statements
                  Gysbert Wassenaar

                  Great, so the variables can be populated from the calendar and time listbox. If you are interested in only some qvd files then create a listbox for the QVD_Id field and select those values you're interested in.

                   

                  If you want to use your complicated if statements expressions instead take a look at the rangesum function.

                    • Re: Summing multiple if statements

                      Thank you for the answer.

                      • Re: Summing multiple if statements

                        Thanks again for your answer. We are quite new to QlikView so we weren't familiar with what all the different expression can do. However, we ended up following your suggestion to use the count function and used this expression below:


                        =count({$< QVD_Id = {2, 9, 10, 11, 13, 15, 17, 18, 19, 20}>}DISTINCT QVD_Id)

                         

                        In short, we were successful in terms of yielding the correct results for day. However, when selecting week or month from the cyclic group we would like to get results that shows the total sum of successfully updated reports for each week/month. We have posted another question in the community (under title, "Summing count function in cyclic group") regarding this.


                        Thanks again for the help