Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using subset in set analysis comparison

Hi Guys,

What I am looking for is a function or a proper syntax to get a 'sub-select' when doing set analysis. In SQL you could write it as:

Select ...

From ...

Where account = (select distinct account from ... where ...);

I need to do an analysis on 'sub population'. I think it should look something like that:

=sum({<cal_date={"$(vDateStart)"}, account={here's the problem}> balance}

My problem is how to get a subset of accounts, I looked at ValueList - but it does not seem to take 'set analysis'? It should be a distinct selection of accounts that meet the following condition:

{$<pacct_close_dt = {"> $(vDateStart)<=$(vDateEnd)"}> + $<pacct_open_eff_dt_original = {">$(vDateStart)<=$(vDateEnd)"}, pacct_open_eff_dt_flag = {1} >}.

Thanks in advance!

And btw. I cannot simply add these conditions to my original sum function - as I will loose few data points that way.

1 Reply
Not applicable
Author

Ok... so my post was premature - I have found the answer. It was p() function (as I suspected ), I just got the syntax wrong when trying it out.

Here is a template from set analysis doc (link: https://community.qlik.com/docs/DOC-4951😞

Syntax: {<Dimension1 = P({< {Set Dimension to select} Dimension1 }>) [, Other
dimensions of the Set]>}


So, I ended up with:

=sum({<pacct_cal_dt={"$(vDateStart)"}, account_no=p({<pacct_close_dt = {"> $(vDateStart)<=$(vDateEnd)"}> + $<pacct_open_eff_dt_original = {">$(vDateStart)<=$(vDateEnd)"}, pacct_open_eff_dt_flag = {1} >} account_no)>} pacct_balance)


Hope it will help somebody one day .

Cheers!

TK