Set analysis - select only records where user selected date is between two pair of dates in the data

[WildCat]

I am brand new to set analysis. My problem is as follows: the user selects a single "DisplayDate" from a calendar filter. In my table chart, I want to display only records where: 1) the selected "DisplayDate" falls between "StartDate1" and "EndDate1" AND 2) the selected "DisplayDate" falls between "StartDate2" and "EndDate2". All four start/end dates exist in the data. I only want records that meet both of those criteria, and I want this to always be the case, regardless of any other selections.  I've been trying, and I know I need to learn more, but I'm afraid I need this answer sooner than I'll be able to master set analysis. Many thanks for any assistance.  

[robert99]

Hi @WildCat 

Sometimes its best to use if. I tried for many hours tro compare 2 dates in set analysis. Sometimes it worked but not always. If was easier and always gave the right result. In your case it would be if with and. Something like sum(If (DisplayDate > StartDate1 and DisplayDate> StartDate2 etc,Field))

When not to use Set Analysis (posted elsewhere)

Example. For example comparing 2 dates

https://community.qlik.com/t5/Qlik-Sense-App-Development/P-and-E-Syntax-issue/td-p/106074

Henric_Cronström

Employee 2018-10-23 10:16 AM

Generally, set analysis cannot be used for a row-by-row comparison this way. The solution with the If() function will work fine, though.

Example from service App where OnTime is measured.

à Count(if  (Call_Resp_Hr + Call_Resp_Min/60 + Call_RHold +1/14400 ) >= Call_RTime,  Call_Num)

Another alternative is to set OnTime up in script

à     if ( (Call_Resp_Hr + Call_Resp_Min/60 + Call_RHold +1/14400 ) >= Call_RTime , ‘Yes’,’No’) as Ontime

and then use set analysis as à {<OnTime = {‘Yes’} >}

[vinieme12]

Option1)

Count({<

Startdate1={"<=$(Date(Max(DisplayDate)))"}

,Enddate1={">=$(Date(Max(DisplayDate)))"}

,Startdate2={"<=$(Date(Max(DisplayDate)))"}

,Enddate2={">=$(Date(Max(DisplayDate)))"}

>}Somemeasure)

Option1)

Where Keyfield is a field that identifies each record uniquely 

Count({<

Keyfield={"=Startdate1<=$(Date(Max(DisplayDate)))

And Enddate2>=$(Date(Max(DisplayDate)))"}

>*<

Keyfield={"=Startdate2<=$(Date(Max(DisplayDate)))

And Enddate2>=$(Date(Max(DisplayDate)))"}

>}Somemeasure)

[WildCat]

Thank you for the suggestions. Robert99, I went with the IF because 1) it was easier 🙂 2) it was mentioned that set analysis may not produce the right result when comparing dates / needing row-by-row processing. It seems to be working. However, I have one remaining problem. The DisplayDate is a user selected date that is selected via a chart filter, it can be changed at any time, and when it's changed, it should return data that meets the new DisplayDate/criteria. Right now, the data does not change based on the selected DisplayDate date.

The expressions I'm using: 

 V_DisplayDate is a variable that sets the default value for that field:

=if(isnull(GetFieldSelections([DisplayDate])), today()',GetFieldSelections([DisplayDate]))

I put the following expression in a dimension field that I added and it gives me YES/NO as expected. So it just seems like a refresh problem? 

= if(date([EndDate1], 'MM/DD/YYYY') >= date(V_DisplayDate,'MM/DD/YYYY') 
AND date([StartDate1], 'MM/DD/YYYY') <= date(V_DisplayDate,'MM/DD/YYYY')
AND date([EndDate2], 'MM/DD/YYYY') >= date(V_DisplayDate,'MM/DD/YYYY')   
AND date([StartDate2], 'MM/DD/YYYY') <= date(V_DisplayDate,'MM/DD/YYYY'),'YES','NO')

Hoping someone can point me in the right direction in regard to how to make the data change based on the selected DisplayDate. Thank you in advance!

[robert99]

@WildCat 

I don't like using variable so can't help you on this

But here's one example comparing a date to a filter selected date (weekending is a filter selected date). I have also put in set analysis that also works. In fact if its comparing a table date to a filter date I mostly use set analysis

Hope this helps

sum({<CanonType = {TransDate} //
,GLTransType = {ARDetail,ARAllocate} ,
Year = , YearFin = , Month = , WeekEnding = , MonthYear = , WeekEnding = , Rebates = , InterCompany =
,WeeksAgo = { ">=$(=MIN(WeeksAgo))"}
//,ARInvDate = {">=$(=num((WeekEnding))-30)"}
>}

if( ARInvDate+30 >= $(=NUM(MAX(WeekEnding))) ,
ARValue) )