Set Analysis - Greater than and Less than problem

[nickking]

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

[nickking]

Sample app now attached.

[#3]

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]

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 · Accepted solution]

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]

result is 2606  is it correct ?

[nickking]

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]

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