Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
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

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 .