Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
eitanyudashkin
Contributor II
Contributor II

Calculating the average between two dates

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.

Capture.PNG

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

Thanks, 

@eitanyudashkin 

 

Labels (1)
1 Solution

Accepted Solutions
Taoufiq_Zarra

@eitanyudashkin 

try this solution:

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

see the following example, I drew the two formulas :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

5 Replies
Taoufiq_Zarra

@eitanyudashkin 

try this solution:

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

see the following example, I drew the two formulas :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
eitanyudashkin
Contributor II
Contributor II
Author

Thank you so much! 

It works now!

eitanyudashkin
Contributor II
Contributor II
Author

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]))

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
eitanyudashkin
Contributor II
Contributor II
Author

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.