Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression to calculate sum of difference between two dates

Hi All,

I'm trying to put down an expression to calculate number of days for the following data. But, the expression is not working properly.

A      B      Date1             Date2

1      1     14/09/2010     28/02/2011

1      2     14/09/2010     13/05/2011

1      3     14/09/2010      17/05/2011

1      4     14/09/2010      17/05/2016


So, now i want to calculate number of days between the two dates for A for different B's and divide it by the count of number of B's for A. The calculation should be like (119+173+175+1480)/4=479.


i have my following expression as:

(AGGR( SUM({1} NetWorkDays(Date1 ,Date2)-1), A)

      /

      AGGR(COUNT({1} DISTINCT B), A))

But with this expression i'm getting different number like 3894.

Can someone suggest or correct me where i'm going wrong?

Any help will be much appreciated.

Kind Regards,

Kshitij

4 Replies
JM
Contributor III
Contributor III

Expression seems alright for the given data. Did you check in your data?

Cheers,

J.

sunny_talwar

‌May be you just need this if A is your dimension

Sum({1} Aggr(NetWorkingDays(Date1, Date2), A, B))/Count({1} DISTINCT B)

Not applicable
Author

Strange. It works fine for the first (/417) but gives weird result for (/57). The last column in the screenshot is just a simple expression to calculate the difference between two dates (NetWorkDays(date1,date2)-1

Example2.png

Any idea why there is output in only one of the rows. the result should be shown in all the rows for A?

sunny_talwar

Try this for Numerator

Sum({1} Aggr(NetWorkingDays(Date1, Date2), A, B)