topic Re: Set Analysis Within Set Analysis (Qlik Sense) in QlikView

Set Analysis Within Set Analysis (Qlik Sense)

groveliam — Mon, 16 Jul 2018 15:45:10 GMT

Hey everyone,

I am working with a set of banking records. I need to be able to count the closed accounts where their close date is within the last month and does NOT have a closing balance of zero. I am able to get the count of the closed within the last month with the following set analysis:

Count({ <Deposits_close_dt={">$(= Date(MonthEnd(AddMonths(GetFieldSelections(([Production Date])),-1)), 'YYYY-MM-DD')) <= $(= Date(([Production Date])))"}> }Distinct acct_nbr)

So I am wondering, do any of you know how to then filter these account to show the count where their closing balances are not zero. I tried an if statement with the above set analysis as the then section, but got no result. Would I be able to do a set analysis within a set analysis? I will try to include some sample data. Any help is appreciated!

Thank you,

Liam Grover

Re: Set Analysis Within Set Analysis (Qlik Sense)

YoussefBelloum — Mon, 16 Jul 2018 15:47:42 GMT

Hi,

try this:

Count({ <Deposits_close_dt={">$(= Date(MonthEnd(AddMonths(GetFieldSelections(([Production Date])),-1)), 'YYYY-MM-DD')) <= $(= Date(([Production Date])))"},acct_nbr={"=sum([closing balances])>0"}> }Distinct acct_nbr)

PS: verify your closing balances field name

Re: Set Analysis Within Set Analysis (Qlik Sense)

sunny_talwar — Mon, 16 Jul 2018 15:48:58 GMT

May be this

Count({<Deposits_close_dt = {">$(=Date(MonthEnd(AddMonths(GetFieldSelections(([Production Date])),-1)), 'YYYY-MM-DD'))<=$(=Date(([Production Date])))"}, [Closing Balance] = {'>0'}>} Distinct acct_nbr)

Re: Set Analysis Within Set Analysis (Qlik Sense)

groveliam — Mon, 16 Jul 2018 16:00:18 GMT

Perfect, Thanks for the fast response!

Re: Set Analysis Within Set Analysis (Qlik Sense)

YoussefBelloum — Mon, 16 Jul 2018 16:02:15 GMT

You're welcome