topic Re: Double aggregation problem in QlikView

Double aggregation problem

Anonymous — Wed, 21 Oct 2015 08:15:11 GMT

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.

Re: Double aggregation problem

Gysbert_Wassenaar — Wed, 21 Oct 2015 12:50:48 GMT

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)

Re: Double aggregation problem

Anonymous — Wed, 21 Oct 2015 13:59:12 GMT

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.

Re: Double aggregation problem

Gysbert_Wassenaar — Wed, 21 Oct 2015 14:11:25 GMT

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

Re: Double aggregation problem

Anonymous — Wed, 21 Oct 2015 15:45:41 GMT

Hi Gysbert,

It was missing the value part as below:

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

Thank you