Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
nickking
New 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
balkumarchandel
Valued Contributor II

Re: Set Analysis - Greater than and Less than problem

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
Honored Contributor III

Re: Set Analysis - Greater than and Less than problem

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!
Partner
Partner

Re: Set Analysis - Greater than and Less than problem

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

Re: Set Analysis - Greater than and Less than problem

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
New Contributor III

Re: Set Analysis - Greater than and Less than problem

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

Partner
Partner

Re: Set Analysis - Greater than and Less than problem

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

Partner
Partner

Re: Set Analysis - Greater than and Less than problem

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

nickking
New Contributor III

Re: Set Analysis - Greater than and Less than problem

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

balkumarchandel
Valued Contributor II

Re: Set Analysis - Greater than and Less than problem

Hi Nick

its better if you can share your sample application ..

very high chance to failure is your date format.

Thanks

BKC

oknotsen
Honored Contributor III

Re: Set Analysis - Greater than and Less than problem

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!