Aggr with set restriction

[danielgrenyer]

Hi

I'm stumped about a formula using an Aggr with a set restriction (or in this case expansion - set of {1}).

I'm trying to pull back the last sales date in a period where there are any sales, regardless of product type.

In my Orderline table I have a field, OrderLineCounter to filter dates to only those dates where there is an order, and I'm applying the {1} set restriction to prevent other product selections restricting this further.

My formula looks like this:

FirstSortedValue( {1 <OrderLineCounter = {1}>} Aggr(date,date),-Aggr(date,date))

It works until the user filters to a product category (or some other dimension) where there are no sales on the last day in the period. However the dates coming back in the Aggr functions still seem to be being restricted by user applied filters.

Anyone got any ideas?

Dan

[danielgrenyer]

Further investigation has reduced the problem (unless I am misunderstanding) to the Aggr function not responding to set restriction.

I have attached a simple qvf to this post that illustrates the issue. It's a simple inline table laod of date, filter (which are fruit) and amount.

Within the sheet is a fruit filter pane. There are two tables with values defined as Aggr({1}Date,Date) and Aggr(Date,Date). I would expect Aggr(Date,Date) to be restricted by values selected in the filter pane...and it is. But Agg({1}Date,Date) is also restricted...which I would not expect.

Any ideas?

Dan

[ogautier62]

Hi

something like :

max({1} SalesDate)

doesn't work ?

regards

[danielgrenyer]

It's part of a wider measure.

I'm trying to sum values but only for the final date in the period where there are any values (regardless of user selection).

In the context of the posted sample App, something like:

FirstSortedValue( {1 <Amount = {'*'}>} Aggr({1} Sum(Amount),Date),-Aggr({1} Date,Date))

So the final Aggr sort descending (so FirstSortedValue gives the last sorted value based on Date) and the first Aggr provides the Value for the measure for that last date....but if a user applies filters, some dates may be filtered out (if there is no fruit for the given period). That's the bit I'm trying to avoid. It needs to keep all dates and in such a case set the value to zero. Instead it is filtering the date to the last date where the fruit exists.

...if that makes sense. If I can get the Aggr function to apply the {1} set restriction I'm sure I can get it to work.

Thanks

[ogautier62]

and just this :

FirstSortedValue({1} sum(Amount), -Date)

it doesn't work ?

[danielgrenyer]

No, unfortunately not - Simple and elegant as it would be QS won't allow nested aggregations.

Hence resorting to using Aggr statements within the FirstSortValue as QS does allow this...