How to show average of days between two dates?

[#1]

Hi,

I have tables A,B and C in script.

A table have columns

[NO],
[ENTER],
[TO],
[DATE],
[CONSULTANTBY],
[COUNTRY_ID]

B Have Columns

[NO],
[OVERDATE],
[START],
[END],
[LOCATION],
[LABEL]

C have Columns

[ADDNO],
[TO_I],
[WORKER]

there is join between three tables. I need to create a straight / pivot table with below:

Dimension1 : [TO] where TO='Available' and [LABEL]= 'All files completed'
Dimension2 : [WORKER]
YEARMONTH : Year and Month this should be from START and END

Expression : Avg([DATE] - [OVERDATE])  expression should show average days of DATE minus OVERDATE

can any one help me with dimension and expression please

Thanks.

[sunny_talwar]

Try this may be:

=Avg(Aggr(DATE - OVERDATE, TO, WORKER, YEARMONTH)) //Assuming that YEARMONTH is also a dimension.

HTH

Best,

Sunny

[PrashantSangle]

Hi,

Use Avg(Interval(Date-OverDate,'D'))

regards

[PrashantSangle]

Or you can try

Avg(Num(Date-OverDate))

Regards

[sunny_talwar]

But wouldn't the DATE - OVERDATE will output a number?

I am not trying to argue that your answer is incorrect, but trying to make sense of the requirement. I don't even know if the solution I proposed will work or not

[PrashantSangle]

Hi,

If DATE and OVERDATE is proper Date in QlikView then whenever it load in QlikView it Convert it into Number therfore i suggest to use num()

even she can try

Avg(Num(Date)-Num(OverDate))

regards

[#7]

Thanks. with out using dimension how to change the expression?

[sunny_talwar]

Did you try one of the solutions proposed by max dreamer‌??? If those did not work, would you be able to share a sample??? Cause I feel you don't need to use aggregate function. The solution should work unless you are using a pivot table and you are looking to get Total row fixed.

Best,

Sunny

[#9]

Thanks. I have used this. for overall average it is showing figure ok. but when I select individual NO the average is showing more. do I need to change anything here? please let me know.

[vvvvvvizard]

Num(Date((Date),'DD/MM/YYYY')-Date(OverDate,'DD/MM/YYYY'))