Chart with different dates and calculations between those two date records

[Anonymous]

Good day,

I have a table which looks as follows:

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

It's easy to do this in a static SQL but not so strait forward in QlikView with dynamic start & end dates...

Then also if at all possible it should also be able to manage null values and possibly get the closest date with values... but maybe lets focus on one problem first.

Your assistance would be highly appreciated.

[SreeniJD · Accepted solution]

Hi There,

You will collect the dates as input variables from the user (lets day date from to date to)... compare this will the existing date field and then have a avg(sum by name)...

Happy to Help

Sreeni

[Anonymous]

Thank you JD

[Anonymous]

Good day,

Further to this post, with the use of variables (date1 and date2) I am able to calculate (BID1 + BID2)/2, 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 ?

Thank you

Edwin