Nested aggregations within set analysis

[#1]

Hi Guys,

Need some help and suggestions

Problem – I want the 3^rd minimum value of the previous day if the current date is selected.

Data example-

dBusinessDate , StartDate_EndDate , L1_Value

01/01/2016 , 2015/02/12 - 2015/02/13 ,  1

01/01/2016 , 2015/02/13 - 2015/02/14 ,  3

01/01/2016 , 2015/02/14 - 2015/02/15 ,  -1

01/01/2016 , 2015/02/15 - 2015/02/16 ,  2

02/01/2016 , 2015/02/14 - 2015/02/15 ,  2

02/01/2016 , 2015/02/15 - 2015/02/16 ,  1

02/01/2016 , 2015/02/16 - 2015/02/17 ,  4

02/01/2016 , 2015/02/17 - 2015/02/18 ,  3

In the example above, if we select dbusinessdate as 02/01/2016, then we should see L1_Value as 2 which is the previous dates third minimum value.

If we use below expression, it would pass the correct previous days value in the set expression but the set of StartDate_EndDate is as per the current date(02/01/2016) whereas it should be a set as per the previous date(01/01/2016).

=num(min(
aggr(sum({$<dBusinessDate={'$(=vPreviousDay)'}>}L1_Value),StartDate_EndDate)),3),'#,##0.00')

While trying below, it says nested aggregation is not allowed (here I am trying to get the set of startenddate for the previous date 01/01/2016)

=num(min(
aggr(sum({$<dBusinessDate={'$(=vPreviousDay)'}>}L1_Value),only({$<StartDate_EndDate={'$(=vPreviousDay)'}>}StartDate_EndDate)),3),'#,##0.00')

Other way I can think of is if we are able to get the third minimum value startenddate and can pass it like below ( Note : the startenddate will change based on a selection on different dimensions for a single businessdate):

= sum({$<dBusinessDate={'$(=vPreviousDay)'},StartDate_EndDate={3^rd Minimum value startenddate}>}L1_Value)

Below Expression works very well  if we are using date as a dimension in a straight/pivot table. (We need to select 2 business dates in this case which is not the business wants )

=num(min(
aggr(sum(L1_Value),dBusinessDate,StartDate_EndDate),3),'#,##0.00')

In all, I am still not able to close this and is over to you now. Please see if you have come across the same sort of problem and can think of some solution. Appreciate your quick help.

Regards,

Vicky

[sunny_talwar]

Would you be able to share a sample to check this out? I have a feeling that you might need to use FirstSortedValue function, but I might be wrong. It might be easier to help with a sample to look at