topic Date Range using Set Analysis in QlikView

Date Range using Set Analysis

john_duffy — Mon, 02 Jan 2012 15:01:06 GMT

Hello All.

I am looking for clarification on two expressions I have created to define a date range using set analysis.

I have attached a sample application with the following two expressions:

Exp1 - Sum({$<Prod_Date = {">=(=min(date(makedate(Ref_Year,Ref_Month,Ref_Day),'YYYY-MM-DD'))) <=$(=max(makedate

(Ref_Year,Ref_Month,Ref_Day),'YYYY-MM-DD')))"}>} Product_Cnt)

Exp2 - Sum( {$<Prod_Date = {">=$(min(date(makedate(Ref_Year,Ref_Month,Ref_Day),'YYYY-MM-DD')))"}

* {"<=$(max(date(makedate(Ref_Year,Ref_Month,Ref_Day),'YYYY-MM-DD)))"}>} Product_Cnt)

If the date range is valid (ie. data is between the min and max dates) both expresssions return the same results. See charts Test1 and Test2 in the attached example.

If both the min and max dates are out of range, both expressions return the same results. See chart Test5 in the attached example.

If the min and max dates don't form a proper range (ie. min > max), Exp1 seems to execute as >= min date or <= max date. Exp2 executes as >= min date and <= max date. Set charts Test3 and Test 4.

Can someone please explain why Exp1 seems to execute as >= min date and <= max date if min and max dates form a valid date range but >= min date or <= max date if min and max dates do not form a valid date range.

Exp2 always executes as >= min date and <= max date.

Thanks,

John.

Date Range using Set Analysis

swuehl — Mon, 02 Jan 2012 18:11:10 GMT

I am currently not able to look into your sample, but I think to remember that this is the standard "intelligent" way QV interprets your search expression, not only in set analysis but also in list box searches.

Date Range using Set Analysis

john_duffy — Tue, 03 Jan 2012 12:52:38 GMT

Thanks for the response. Let me know if you are able to open the sample application. I am using QV10 SR2.

Date Range using Set Analysis

swuehl — Tue, 03 Jan 2012 16:08:17 GMT

John,

I now had a short look into your app. As I tried to say, I think that the behaviour you've observed is kind of the "normal", "intelligent" way QV interprets your searches. For simplicity, assume we have a field with some numbers (like Dates in numerical representation).

If you just click on the caption and start typing, you can enter

>10<20

in the search bar, this will return the values between 10 and 20 (excluding), like a AND: =if(Value>10 AND Value<20, Value)

<20>10

will do the same.

<10>20

will return the values lower than 10 OR larger than 20 ( =if(Value< 10 or Value > 20, Value) )

I guess QV is really just trying to interpret what the user intends, maximizing the circumstances where it will return ANYTHING. Note that we don't use any logical operator here, so QV may be free to guess ..

And as I believe, the search function is one of least/worst documented part in the software, so nobody can blaim on "different from documentation!"

Result could get pretty much off from your expectations, though.

If you want to enforce the AND condition, you could use it explicitely, something along the lines:

=Sum({$<Prod_Date = {"=Prod_Date>=$(=num(min(makedate(Ref_Year,Ref_Month,Ref_Day)))) and Prod_Date<=$(=num(max(date(makedate(Ref_Year-1,Ref_Month,Ref_Day)))))"}>} Product_Cnt)

for your example (or as you are already using: use intersection operator * for the element sets )

Not sure if this answers your question.

Regards

Stefan

Date Range using Set Analysis

john_duffy — Tue, 03 Jan 2012 16:45:25 GMT

Excellent explanation Stefan.

You are correct, QlikView seems to try to interpret the condition. In my case, I will be using min and max of the same date so I will always have a vaild range.

Thanks for your help,

John.