topic Re: Nested Aggr and Set Analysis in QlikView

Nested Aggr and Set Analysis

Anonymous — Fri, 19 Jun 2015 17:41:34 GMT

I am dealing with some financial data which basically requires plotting the last value of a field/measure(NAV) in a month (hence the firstsortedvalue by -ve weight of the period_end_date) in a series of charts.

The formula I've used to accomplish this is:

aggr(Sum(aggr(FirstSortedValue((END_MONTH_NAV),-PERIOD_END_DATE),PORTFOLIO_CD,MonthYear)),MonthYear)

where PORTFOLIO_CD and MonthYear are the dimensions to slice the data. I now need to put in a set condition on one of the charts to ignore user selection on the PERIOD_END_DATE or have it be <= selected period end date. I'm having some issues figuring out where exactly the {<PERIOD_END_DATE =>} should go in this this statement?

aggr(Sum({<PERIOD_END_DATE =>} aggr(FirstSortedValue((END_MONTH_NAV),-PERIOD_END_DATE),PORTFOLIO_CD,MonthYear)),MonthYear)

The above statement does not seem to work: any pointers would be much appreciated, thanks.

Re: Nested Aggr and Set Analysis

sunny_talwar — Fri, 19 Jun 2015 17:44:31 GMT

Try this:

=Aggr(Sum({<PERIOD_END_DATE =>} Aggr(FirstSortedValue({<PERIOD_END_DATE =>}(END_MONTH_NAV),-PERIOD_END_DATE),PORTFOLIO_CD,MonthYear)),MonthYear)

Re: Nested Aggr and Set Analysis

swuehl — Fri, 19 Jun 2015 17:45:55 GMT

Not sure if I fully understood your issue, it would be good if you could upload a small sample QVW if needed.

You may need to add the set expression to each aggregation function, including an implicite outer only():

= only({<PERIOD_END_DATE =>}

aggr(Sum({<PERIOD_END_DATE =>}

aggr(FirstSortedValue({<PERIOD_END_DATE =>} (END_MONTH_NAV),PERIOD_END_DATE)

,PORTFOLIO_CD,MonthYear)

),MonthYear)

)

Re: Nested Aggr and Set Analysis

Anonymous — Fri, 19 Jun 2015 17:49:21 GMT

That worked, thanks a ton

Re: Nested Aggr and Set Analysis

sunny_talwar — Fri, 19 Jun 2015 17:51:13 GMT

No problem

I am glad I was able to help.

Best,

Sunny

Re: Nested Aggr and Set Analysis

Anonymous — Fri, 19 Jun 2015 21:24:48 GMT

So not quite sure what happened there, but I guess I called it too soon, it seems,

Aggr(Sum({<PERIOD_END_DATE =>} Aggr(FirstSortedValue({<PERIOD_END_DATE =>}(END_MONTH_NAV),-PERIOD_END_DATE),PORTFOLIO_CD,MonthYear)),MonthYear) still does not ignore the selections (another sheet in this app has clear selections enabled which caused me to think the issue had been resolved , while I worked on another app). Using Only will not work here since I want it to return all the values for the last nav update in each month from inception.

Nested Aggr and Set Analysis

Re: Nested Aggr and Set Analysis

swuehl — Fri, 19 Jun 2015 21:30:31 GMT

In your original post, you used the outer aggr() without an outer aggregation function, this will lead to an implicite only() function applied. Have you tried with the only() + set expression?

Your expression also only clears selections in one field, maybe you need to consider other fields to clear?

If this doesn't work, could you upload a small sample QVW that demonstrates this issue?

Re: Nested Aggr and Set Analysis

Anonymous — Fri, 19 Jun 2015 21:36:30 GMT

I got it to work using max using your suggestions, but only works as well. I guess I need to delve deeper into how only works. Verified it really works this time

Thanks again for all your help.