Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
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

balkumarchandel
Specialist II
Specialist II

will suggest to use

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

BKC

balkumarchandel
Specialist II
Specialist II

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

balkumarchandel
Specialist II
Specialist II

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.

balkumarchandel
Specialist II
Specialist II

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