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

[oknotsen]

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.

[manojkulkarni]

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)

[#4]

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]

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

[manojkulkarni]

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

[manojkulkarni]

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

[nickking]

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]

Hi Nick

its better if you can share your sample application ..

very high chance to failure is your date format.

Thanks

BKC

[oknotsen]

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.