topic Re: Chart with different dates and calculations between those two date records in QlikView

Chart with different dates and calculations between those two date records

Anonymous — Fri, 16 Oct 2015 08:59:39 GMT

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.

Re: Chart with different dates and calculations between those two date records

SreeniJD — Fri, 16 Oct 2015 09:04:00 GMT

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

Re: Chart with different dates and calculations between those two date records

Anonymous — Fri, 16 Oct 2015 10:28:07 GMT

Thank you JD

Re: Chart with different dates and calculations between those two date records

Anonymous — Tue, 20 Oct 2015 11:38:39 GMT

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