Pass Parameters into Set Analysis (Qlik Sense)

[rfosterr]

Being relatively new to Qlik the set functionality appears to be very powerful. For my scenario I have encountered an issue with passing the begin and end dates into the functions. The scenario is a classic joiners, leavers, stayers situation. If I provide literal dates the joiner, leaver, and stayer calculations are correct. I have been able to pass the begin date into the calculation and obtain the correct answers. However if I use the same syntax for the begin and end dates the calculations return 0 (zero). My guess is that I am fouled up in the $ and/or = expansion usage.

The scenario is to return the numbers of joiners, leavers, and stayers based upon the filter criteria selected by the user. The simple scenario is provide selection for the date of the data (Report_Date) and account risk rating to determine how many accounts (Acct) left, joined, or stayed a specific risk rating between two dates.

The following successfully returns the number of accounts that left a risk group (determined by filtering on a specific risk rating):

count ( distinct {$ < Acct= p({<Report_Date={'7/31/2017'}>} Acct) - p({<Report_Date={'8/31/2017'}>} Acct)>} Acct)

Either of the following also successfully returns the number of accounts that left a risk group (determined by filtering on a specific risk rating). The calculation uses a literal for the end date but the begin date is based upon the minimum Report_Date provided by a filter.

count ( distinct {$ < Acct= p({<Report_Date="=SubField(GetFieldSelections([Report_Date],',',GetSelectedCount([Report_Date])),',',1)"}>} Acct) - p({<Report_Date={'8/31/2017'}>} Acct)>} Acct)

or

count ( distinct {$ < Acct= p({$<[Report_Date]={"=min([Report_Date])"}>} Acct) - p({<Report_Date={'8/31/2017'}>} Acct)>} Acct)

But both of the following return 0 for the number of leavers:

count ( distinct { < [Acct]= p({$<[Report_Date]={"=min([Report_Date])"}>} Acct)-p({<$[Report_Date]={"$=(=max([Report_Date]))"}>} Acct)>} Acct)

or

count ( distinct {$ < Acct= p({<Report_Date="=SubField(GetFieldSelections([Report_Date],',',GetSelectedCount([Report_Date])),',',1)"}>} Acct) -

p({<Report_Date="=SubField(GetFieldSelections([Report_Date],',',GetSelectedCount([Report_Date])),',',GetSelectedCount([Report_Date]))"}>} Acct)>} Acct)

The start and end dates display correctly whether calculated with the min/max or the subfield  syntax.

I have attached the excel data source and the qvf of the test app.

Thanks for the help.

[sunny_talwar]

Try this

Count(DISTINCT {<[Acct] = p({$<[Report_Date] = {"$(=Max([Report_Date]))"}>} Acct) - p({$<[Report_Date]={"$(=Date(Floor(MonthEnd(Max([Report_Date]), -1))))"}>} Acct)>} Acct)

[rfosterr]

If I understand the syntax, {"$(=Date(Floor(MonthEnd(Max([Report_Date]), -1))))"}>} is providing one month prior to the max(Report_Date). That will not always be the case as the user needs to be able to choose any date to be the starting set. Since the filter in Qlik Sense cannot be limited to just 2 values I have to be able to provide the lowest and the highest date selected for the sets (for those situations when the user inadvertently enables more than two dates). The min(Report_Date) would be the beginning set and max(Report_Date) would be the ending set. Leavers would the (min - max) and joiners would be the (max - min).

[rfosterr]

I took the answer supplied by Sunny and twisted it a bit. I have attached the QVF that works. The data source attached in original post remains the same.

Leavers

Count(DISTINCT {<[Acct] = p({$<[Report_Date] = {"$(=Min([Report_Date]))"}>} Acct) -

p({$<[Report_Date]={"$(=Max([Report_Date]))"}>} Acct)>} Acct)

Joiners

Count(DISTINCT {<[Acct] = p({$<[Report_Date] = {"$(=Max([Report_Date]))"}>} Acct) -

p({$<[Report_Date]={"$(=Min([Report_Date]))"}>} Acct)>} Acct)

Stayers

Count(DISTINCT {<[Acct] = p({$<[Report_Date] = {"$(=Max([Report_Date]))"}>} Acct) -

e({$<[Report_Date]={"$(=Min([Report_Date]))"}>} Acct)>} Acct)

Number of Accts (at end of comparison period)

Count(DISTINCT {$<[Acct]= p({<[Report_Date] ={"$(=Max([Report_Date]))"}>}Acct)>}Acct)

!

[sunny_talwar · Accepted solution]

Try these

Leavers

Count(DISTINCT {<[Acct] = p({$<[Report_Date] = {"$(=Min([Report_Date]))"}>} Acct) - p({$<[Report_Date]={"$(=Max([Report_Date]))"}>} Acct)>} Acct)

Joiners

Count(DISTINCT {<[Acct] = p({$<[Report_Date] = {"$(=Max([Report_Date]))"}>} Acct) - p({$<[Report_Date]={"$(=Min([Report_Date]))"}>} Acct)>} Acct)