Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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