topic Re: Summing multiple if statements in Qlik Learning Discussions

Summing multiple if statements

Fri, 19 Sep 2014 11:06:40 GMT

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 — Fri, 19 Sep 2014 11:23:44 GMT

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

Fri, 19 Sep 2014 12:09:43 GMT

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 — Fri, 19 Sep 2014 12:25:48 GMT

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

Fri, 19 Sep 2014 12:34:19 GMT

Thank you for the answer.

Re: Summing multiple if statements

Tue, 23 Sep 2014 07:25:16 GMT

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