Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nickking
Contributor III
Contributor III

Set Analysis - Greater than and Less than problem

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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)))

View solution in original post

16 Replies
oknotsen
Master III
Master III

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.

May you live in interesting times!
manojkulkarni
Partner - Specialist II
Partner - Specialist II

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)

Not applicable

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)'}

nickking
Contributor III
Contributor III
Author

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

manojkulkarni
Partner - Specialist II
Partner - Specialist II

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

manojkulkarni
Partner - Specialist II
Partner - Specialist II

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

nickking
Contributor III
Contributor III
Author

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

Anonymous
Not applicable

Hi Nick

its better if you can share your sample application ..

very high chance to failure is your date format.

Thanks

BKC

oknotsen
Master III
Master III

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.

May you live in interesting times!