Using subset in set analysis comparison

Tue, 09 Feb 2016 10:27:17 GMT

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.

Re: Using subset in set analysis comparison

Tue, 09 Feb 2016 10:58:18 GMT

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!

topic Re: Using subset in set analysis comparison in QlikView

Using subset in set analysis comparison

Re: Using subset in set analysis comparison