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

[gwenngannon]

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

[marcus_sommer]

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

- Marcus

[sunny_talwar]

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

[Oleg_Troyansky]

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.

[gwenngannon]

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

[Oleg_Troyansky]

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