Double aggregation problem

[Anonymous]

Good day,

I had an initial problem in comparing data from two dates as per a previous request:

NAME       DATE              BID Price         ASK Price

ABC          2015/10/01     0.73236           0.7255

ABC          2015/10/02     -                       -

ABC          2015/10/03     0.7435            0.744

DEF          2015/10/01     1.23434          1.2983

DEF          2015/10/02     1.2355            1.30121

The user would like to select different dates, i.e. a start and end date.  From these dates I need to perform a calculation on for example the BID prices.

Therefore if ABC is selected and dates i Oct 2015 and 3 Oct 2015, then I need to show a calculation like this:

New desired chart:

NAME          BID Price 1          BID Price 2     (BID1 + BID2) /2

ABC             0.73236               0.7435            0.73793

This was resolved by making use of two data variables.

Further to this problem, with the use of variables (date1 and date2) I am able to calculate (BID1 + BID2)/2 for the specified two dates, however I am required to, if there is no record for the "variable" date, find the closes date in the past which does exist for the DIM.

I tried something like this:

max(if(DATE = date(max(if(DATE <= '$(date1)',DATE))),BID,null()))

However I can not perform multiple or sub aggregations in the current aggregations.

So my question is, how can I check for the closest existing date record to the specified variable date ?

i.e. if the user specifies 2015/10/05 as a date2, it should find 3 Oct 2015 of ABC and 2 Oct 2015 for DEF.

[Gysbert_Wassenaar]

Perhaps like this:

BID Price 1: FirstSortedValue({<[BID Price]={'>0'}>}, DATE)

BID Price 2: FirstSortedValue({<[BID Price]={'>0'}>}, -DATE)

(BID1 + BID2) /2: rangeavg(BID Price 1,BID Price 2)

[Anonymous]

Hello Gysbert,

The syntax of the above statement seems to be missing something.  QV complains about a ).

Am I understanding correctly that the statement will check where the BID Price is bigger than 0.

Can one maybe use a <> null() statement ?

Otherwise it seems to work, except for the fact that null() values are also taken into account if I use a simple:

FirstSortedValue([BID Price], DATE)

Thank you for this.

[Gysbert_Wassenaar]

Which of the above? I posted three epxressions. And all have matching opening and closing parentheses afaict.

[Anonymous]

Hi Gysbert,

It was missing the value part as below:

FirstSortedValue({<[BID_Price]={'>0'}>}BID_Price, DATE)

Thank you