Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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') )
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') )