topic Set Analysis - Greater than and Less than problem in QlikView

Set Analysis - Greater than and Less than problem

nickking — Tue, 14 Apr 2015 11:10:21 GMT

Hi,

I'm looking for a little help getting some set analysis to work. I have a date field that tells me when a decision was made (Last_Est_Dec_Date) and I want to compare it to a period defined by two more dates (TSVERSION_START and TSVERSION_END).

If my decision date is greater than the start date and less than the end date and the team name matches my first modifier of SplitFunc_CareTeam then I can count the decisions made in that team. I get a result if I only use the start date but as soon as I add the end date in I'm returning a zero. I know from writing this as a Sum If formula that there are a couple of thousand cases that match the criteria.

=sum({$<SplitFunc_CareTeam={'Team 1'}, Last_Est_Dec_Date={"'>=$(TSVERSION_START) <=$(TSVERSION_END)"}>} ClaimantCount)

Can anybody spot where I'm going wrong? From looking at other posts this seems to be what works for other people but most definitely doesn't work for me.

Thanks very much

Nick

Re: Set Analysis - Greater than and Less than problem

oknotsen — Tue, 14 Apr 2015 11:13:38 GMT

Are you sure it has to be fixed with Set Analysis?

This sounds like something that you could also solve by using the IntervalMatch in the script.

Re: Set Analysis - Greater than and Less than problem

manojkulkarni — Tue, 14 Apr 2015 11:16:14 GMT

in quick look, i found syntax error in first expression, where in you have single quote. which i removed in below expression.

sum({$<SplitFunc_CareTeam={'Team 1'}, Last_Est_Dec_Date={">=$(TSVERSION_START) <=$(TSVERSION_END)"}>} ClaimantCount)

Re: Set Analysis - Greater than and Less than problem

Tue, 14 Apr 2015 11:20:45 GMT

Hello,

it is most likely a problem with date format. Be sure to get same date format output for the 2 variables as is used for your decision date.

I prefer to debug such set analysis expressions by not setting an formula name for this expression then the $ - parts are eveluated in the column title and and errors are easier to spot.

And it seems you got more quotings at the start of the expression than at the end of the date.

I got a similar expression to calculate some revenues which looks like:

%KalenderDatum={'>=$(vMinDatum) <=$(vMaxDatum)'}

Re: Set Analysis - Greater than and Less than problem

nickking — Tue, 14 Apr 2015 11:28:19 GMT

I have removed the extra quote at the start and it still isn't working.

Re: Set Analysis - Greater than and Less than problem

manojkulkarni — Tue, 14 Apr 2015 11:30:32 GMT

if it is not confidential, can you post application with sample data

Re: Set Analysis - Greater than and Less than problem

manojkulkarni — Tue, 14 Apr 2015 11:31:23 GMT

make sure both your variables & date field being compared should be in same format

Re: Set Analysis - Greater than and Less than problem

nickking — Tue, 14 Apr 2015 11:32:27 GMT

Hi Onno,

I'm not familiar with Interval Match. Would it work if the start and end dates are loaded in one dataset and the decision date is brought in later in a second dataset?

Thanks
Nick

Re: Set Analysis - Greater than and Less than problem

Anonymous — Tue, 14 Apr 2015 11:37:29 GMT

Hi Nick

its better if you can share your sample application ..

very high chance to failure is your date format.

Thanks

BKC

Re: Set Analysis - Greater than and Less than problem

oknotsen — Tue, 14 Apr 2015 12:36:37 GMT

It would have the start and end date in a table, which effectively are classifications.

You would have the decision date in a different table.

It will than check for every decision if it fits between the start and end date.

See attachment.

Re: Set Analysis - Greater than and Less than problem

nickking — Tue, 14 Apr 2015 13:00:40 GMT

Sample app now attached.

Re: Set Analysis - Greater than and Less than problem

Tue, 14 Apr 2015 13:09:43 GMT

Hi,

i didnt really check how your calendar is linked to the facts but

=sum({$<SplitFunc_CareTeam={'Team 1'}, Last_Est_Dec_Date={">=$(=MIN(TSVERSION_START)) <=$(=max(TSVERSION_END))"}>} ClaimantCount)

is working for me.

Putting the date range into aggregation functions eliminates the problems with multiple start or end dates selected.

As mentioned, if you get rid of the expression label, you will see if a date is eveluated into the set analysis expression :

=sum({$<SplitFunc_CareTeam={'Team 1'}, Last_Est_Dec_Date={">=10/09/2007 <=41908"}>} ClaimantCount)

Obviously there still is an issue with date formatting

Re: Set Analysis - Greater than and Less than problem

Anonymous — Tue, 14 Apr 2015 13:11:36 GMT

will suggest to use

=sum(if(SplitFunc_CareTeam='Team 1', if(Last_Est_Dec_Date>=TSVERSION_START and TSVERSION_START<=TSVERSION_END,ClaimantCount)))

BKC

Re: Set Analysis - Greater than and Less than problem

Anonymous — Tue, 14 Apr 2015 13:16:57 GMT

my mistake use this

=sum(if(SplitFunc_CareTeam='Team 1', if(Last_Est_Dec_Date>=TSVERSION_START and Last_Est_Dec_Date<=TSVERSION_END,ClaimantCount)))

Re: Set Analysis - Greater than and Less than problem

Anonymous — Tue, 14 Apr 2015 13:17:47 GMT

result is 2606 is it correct ?

Re: Set Analysis - Greater than and Less than problem

nickking — Tue, 14 Apr 2015 13:42:30 GMT

Hi,

That is the result. You'll see from the attached app that I have a working Sum If calculation but I'm trying to get a working Set Analysis in order to remove the effect of any selections in the date and team fields from the calculation.

Re: Set Analysis - Greater than and Less than problem

Anonymous — Tue, 14 Apr 2015 14:29:37 GMT

as it is line by line comparison you can not use set analysis.