Calculation of Dates

[#1]

I have the follow scenario:

Activity	Start_Date	End_Date	Duration (days)
Education	31/12/2013	01/01/2014	1
Sports	10/12/2013	14/12/2013	5
Shopping	12/04/2013	12/05/2013	30
Others	15/12/2013	16/12/2013	1
Sports	12/03/2013	12/04/2013	31
Education	12/02/2013	12/04/2013	59
Others	12/02/2013	01/03/2014	382

First i bring in these data from Excel and have them formatted to DD/MM/YYYY format.

When creating this straight table chart, i added in an expression to calculate the Duration through: (End_Date - Start_Date).

Next i would like to put this into a bar chart with a sum to calculate the total number of days the activity took.

Eg. Sports will be 5+31=36, Others will be 1+382=383 etc etc

Is there a single-line expression to do this? I tried having the following expression but it doesn't work:

Sum(TOTAL ( [End_Date] - [Start_Date] ) )

Can someone enlighten me please?

[chiru_thota · Accepted solution]

You can use below expression.You need correct the date format first as QV is considering few dates as text as highlighted.

sum(TOTAL <Activity>

DATE(MakeDate(Right(Trim(End_Date),4),SubField(End_Date,'/',2),SubField(End_Date,'/',1)),'DD/MM/YYYY') -

DATE(MakeDate(Right(Trim(Start_Date),4),SubField(Start_Date,'/',2),SubField(Start_Date,'/',1)),'DD/MM/YYYY') )