Calculating difference between the last and first value only of a group.
Hello I'm stumped on this issue, I have body weight data grouped by persons and I need to calculate the weight loss/ gain between their first and their last visit while at the same time I need to show data for all visits. This is what my report needs to
look like. Thanks in Advance
Name: John Doe (GROUPED BY)
RowCount Visit Date Weight
1 01/15/2011 180
2 02/15/2011 175
3 03/15/2011 177
4 04/15/2011 173
Weight Gained/ Loss 7 (sum(180-173) this is the value I need)
Name: Jane Doe (GROUPED BY)
RowCount Visit Date Weight
1 01/15/2011 130
2 02/15/2011 125
3 03/15/2011 120
Weight Gained/ Loss 10 (sum(130-120) this is the value I need)
August 9th, 2011 7:44pm
Here's how I'd do it ...
Create Dataset: select name, visit_date, weight from tbl_name order by name, visit_date
Create a matrix In the rows section, drag name, and then visit_date In the Data section, drag weight Then go down to your Row Groups section, and you should see a row group called "visit_date". Right-click and select "Add Total"
Edit the expression for Add Total under the weight column and change to =Max(Fields!weight.Value) - Min(Fields!weight.Value)
Done!
I hope this helps.
Potential bug: Assumes that each weight value is less than the previous weight value.
DJAnsc
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2011 8:47pm
USE THIS,
SELECT
*
INTO #TEST
FROM
(
select
'ABC'
AS CName,'01/15/2011'
as VisitDate,
180 AS
Weight
UNION
ALL
SELECT
'ABC'
AS CName,'02/15/2011'
as VisitDate
, 175
AS
Weight
UNION
ALL
SELECT
'ABC'
AS CName,'03/15/2011'
as VisitDate
, 177
AS
Weight
UNION
ALL
SELECT
'ABC'
AS CName,
'04/15/2011'
as VisitDate
, 173
AS
Weight
UNION
ALL
select
'ABC1'
AS CName,'01/15/2011'
as VisitDate,
176 AS
Weight
UNION
ALL
SELECT
'ABC1'
AS CName,'02/15/2011'
as VisitDate
, 175
AS
Weight
UNION
ALL
SELECT
'ABC1'
AS CName,'03/15/2011'
as VisitDate
, 177
AS
Weight
UNION
ALL
SELECT
'ABC1'
AS CName,
'04/15/2011'
as VisitDate
, 190
AS
Weight)
AS A
/*****************************************************************************************************************************************/
SELECT A.CName
,SUM(CASE
WHEN A.MINDATE
= B.VisitDate
THEN
Weight
ELSE 0
END)
AS FIRSTWeight
,
SUM(CASE
WHEN A.MAXDate
= B.VisitDate
THEN
Weight
ELSE 0
END)
AS LASTWeight
,
SUM(CASE
WHEN A.MAXDate
= B.VisitDate
THEN
Weight
ELSE 0
END)
-
SUM(CASE
WHEN A.MINDATE
= B.VisitDate
THEN
Weight
ELSE 0
END)
AS WeightGain_Loss
FROM
(SELECT
CName,
MIN(VisitDate)
AS MINDATE,MAX(VISITDate)
AS MAXDate
FROM #TEST
GROUP
BY CName)
AS A
JOIN #TEST B
ON A.CName
= B.CName
GROUP
BY A.CName
/********************************************************************************************************************************************/
DROP
TABLE #TEST
Let me know if you have any issues implementing this in your table.
August 9th, 2011 8:50pm
You may try
=Last(Fields!weight.Value) - First(Fields!weight.Value)Remember to mark as an answer if this post has helped you.
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2011 8:57pm
That's Perfect Thank You
August 9th, 2011 9:17pm
This topic is archived. No further replies will be accepted.
Other recent topics
