Calculating the average between two dates

[eitanyudashkin]

Hi!

I am trying to find the average between two dates values of different project with this expression:

avg((date(today() - [Contract receiving date])))

Qlik Sense displays wrong value as there are multiple WBS items under each contact. Attached below a screenshot of my database. Qlik calculates the average for all the WBS items in the following way - (51*4+56*8+80*3)/15=59. However, I need it to calculate the difference based on each one of the unique contracts in the following way - (51+56+80)/3=62.333.

I would really appreciate if anyone can assist with an expression that would calculate the correct average for the difference in dates!

Thanks, 

@eitanyudashkin 

[Taoufiq_Zarra · Accepted solution]

@eitanyudashkin 

try this solution:

aggr(avg((date(today() - [Contract receiving date]))),[contract number])

see the following example, I drew the two formulas :

[eitanyudashkin]

Thank you so much! 

It works now!

[eitanyudashkin]

I have one more question, is it possible to represent that calculated average in as a KPI? 

[Taoufiq_Zarra]

Do you mean :

=avg(aggr(avg((date(today() - [Contract receiving date]))),[contract number]))

[eitanyudashkin]

Yes, my question is if I can represent this number in a KPI. 

I tried putting the formula in the KPI but it would not display the number for some reason.