topic Set analysis inside a Set analysis less than equal to a variable in QlikView

Set analysis inside a Set analysis less than equal to a variable

gwenngannon — Sat, 22 Oct 2016 01:36:27 GMT

Hello to all of the Set analysis Experts:

I am attempting to sum the Absolute values of Transactions between a period of time (vCyclicMaxDate),(vCyclicMinDate)

that when aggregated by the dimension are greater than or equal to vCyclic Sum

(so for example if a specified " DIMENSION' has 7 transactions , both credits and debits, between (vCyclicMaxDate),(vCyclicMinDate that when summed by absolute value exceed

an input (Cyclic Sum) - I want to return the total absolute sum of all the 7 transactions

The dimension is cyclic between Emails, TaxId , Names

I attempted to place a set analysis inside of another set analysis for the expression:

First I summed up the absolutes values of transactions between the dates - then tried to compare that to the vCyclicSum

=sum ({< (sum({< EFF_DATE ={"<=$(=vCyclicMaxDate)>=$(=vCyclicMinDate)"}>}fabs(TXN_AMOUNT))) = {">= $(=vCyclicSum)"} >}fabs(TXN_AMOUNT))

Is my expression lacking a quote or Parenthesis or is my approach completely incorrect?

Thanks to anyone tht can help

Re: Set analysis inside a Set analysis less than equal to a variable

marcus_sommer — Sat, 22 Oct 2016 13:34:40 GMT

You need an aggr() for nesting aggregations: AGGR...

- Marcus

Re: Set analysis inside a Set analysis less than equal to a variable

sunny_talwar — Sat, 22 Oct 2016 14:03:13 GMT

I think it would be easier to help if you can provide a sample with expected output

Re: Set analysis inside a Set analysis less than equal to a variable

Oleg_Troyansky — Sun, 23 Oct 2016 15:43:00 GMT

I'd agree that it would be a lot easier with the sample. Let me take a shot at it without one.

You are tryping to select a "DIMENSION" with the condition that the sum of Amount for this DIMENSION within a range of dates is less than a certain value stored in a variable, right? You can calculated it using Set Analysis with an Advanced Search condition. You don't need AGGR for this purpose because Advanced Search includes an implied AGGR. So, the syntax should look approximately like this:

=sum ({< DIMENSION={"=sum({< EFF_DATE ={"<=$(=vCyclicMaxDate)>=$(=vCyclicMinDate)"}>}fabs(TXN_AMOUNT))>=$(vCyclicSum)"} >}

fabs(TXN_AMOUNT))

A few assumptions for this expression to work:

- DIMENSION must be a field, not another formula.

- the two date variables must be formatted with the same date format as your EFF_DATE field.

- vCyclicSum contains a value, not another formula.

This is the kind of complex Set Analysis solutions that we cover at the Masters Summit for Qlik (watch for our announcements for future Summits).

Cheers,

Oleg Troyansky

Learn advanced QlikView and Qlik Sense techniques from my book QlikView Your Business.

Re: Set analysis inside a Set analysis less than equal to a variable

gwenngannon — Tue, 25 Oct 2016 12:27:21 GMT

Thank you all for your responses, the knowledge is appreciated..

Here is the current expression to expand on ( I have tried to add in an inner set analysis to aggregate by vCyclic group but cannot seem to correctly write the syntax)

=sum ({< (sum({< EFF_DATE ={"<=$(=vCyclicMaxDate)>=$(=vCyclicMinDate)"}>}fabs(TXN_AMT))) = {">= $(=vCyclicSum)"} >}fabs(TXN_AMT))

Below is the result I'm looking for : when vCyclicSum > = 1800.00 mindate 06/2016 max date 06/30/2016

I am looking to return all txn and details that aggregate to the input criteria on the Dimension level

Hope this makes sense - Thanks for any assistance

Re: Set analysis inside a Set analysis less than equal to a variable

Oleg_Troyansky — Thu, 27 Oct 2016 17:13:18 GMT

Hi Gwenn,

this syntax won't fly... In Set Analysis, any filter should look like this:

Field = {Value} or

Field = {"Search"} or

Field = {"=any condition"}

So, if you need to select _____ (Dimension) based on the condition that SUM(something)>=TargetValue, then your Set Analysis Expression should look like this:

sum(

{<DIMENSION={"=SUM(... something)>=vCyclicSum"}>}

fabs(TXN_AMT))

assuming that vCyclicSum holds a value and not another formula.

cheers,

Oleg Troyansky

Learn Set Analysis and many other advanced QlikView and Qlik Sense techniques in my book QlikView Your Business