Above Function with Dates

Mon, 14 Sep 2015 18:06:26 GMT

Hello,

I am new to qlikview and am struggling to understand these results :

ReqDate Interval :

=avg(aggr(date#(SALDOC_REQDATE,'DD.MM.YYYY')-Above(date#(SALDOC_REQDATE,'DD.MM.YYYY')),SOLDTO,SALDOC_REQDATE))

=> Results in this example seem fine

OrderDate Interval :

=avg(aggr(date#(SALDOC_DATE,'DD.MM.YYYY')-Above(date#(SALDOC_DATE,'DD.MM.YYYY')),SOLDTO,SALDOC_DATE))

Results here are not correct and noticed that -514 is the interval between first date and last date in range.

Any help in correcting my expressions are very much appreciated.

I would just like to get the average interval between consecutive transactions in calendar days (working days would be great!)

Thanks for your help !

David

Re: Above Function with Dates

Oleg_Troyansky — Mon, 14 Sep 2015 20:26:52 GMT

Assuming that both dates are used as chart dimensions, I think you don't need Advanced Aggregation here. Something like the following should work:

=date#(SALDOC_REQDATE,'DD.MM.YYYY') -

Above(date#(SALDOC_REQDATE,'DD.MM.YYYY'))

This, expression, however, won't produce any totals. If the goal is to get the accurate totals, then the AGGR() is necessary, but then you have to include all of your chart dimensions into the list of your AGGR() dimensions.

In order to get work days only, try using the NetWorkDays() function instead of a simple subtraction.

If you are still having problems, please post an example that we could use to build a solution for you.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

topic Above Function with Dates in QlikView

Above Function with Dates

Re: Above Function with Dates