topic Simple Date Comparison Example in Set Analysis in QlikView

Simple Date Comparison Example in Set Analysis

Tue, 05 Nov 2013 18:06:41 GMT

Could someone please tell me if it is possible to use set analysis in this very simple sum example? Just trying to sum up Hours IF the HourDate is between Date1 and Date2. I am having trouble getting this to work on a large dataset where using If/Then is far too slow and not an option)

I am afraid this is impossible in set analysis, if it is, I would really appreciate suggestions!

THANK YOU!

Re: Simple Date Comparison Example in Set Analysis

Gysbert_Wassenaar — Tue, 05 Nov 2013 18:12:23 GMT

If Date1 and Date2 are field names instead of variables then you probably won't get the results you want. The set is calculated per chart, not per row. See this document for an explanation: set_analysis_intra-record.qvw

If the fields are all in the same table you can create a field in the script that flags if the condition is true or false:

load Date1, Date2, HourDate, Hours, Project,

if(HourDate >=Date1 and HourDate<=Date2,1,0) as Flag

from ...

You can then use that flag field in the set analysis expression: sum({<Flag={1}>}Hours)

Re: Simple Date Comparison Example in Set Analysis

Tue, 05 Nov 2013 19:19:53 GMT

Thanks, the referral to the example : set_analysis_intra-record.qvw did the trick! In this case I am able to use a unique index on the data rows so the Flag is not needed, although that's a great idea as well.

For the benefit of anyone coming along later, here is a picture of the final solution: