Nested Aggr and Set Analysis

[Anonymous]

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.

[sunny_talwar]

Try this:

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

[swuehl]

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)

)

[Anonymous]

That worked, thanks a ton

[sunny_talwar]

No problem

I am glad I was able to help.

Best,

Sunny

[Anonymous]

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

[swuehl · Accepted solution]

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?

[Anonymous]

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.