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

16 Replies
nickking
Contributor III
Contributor III
Author

Sample app now attached.

Not applicable

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

Anonymous
Not applicable

will suggest to use

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

BKC

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

Anonymous
Not applicable

result is 2606  is it correct ?

nickking
Contributor III
Contributor III
Author

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.

Anonymous
Not applicable

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