6 Replies Latest reply on Jul 17, 2017 5:48 AM by Ritesh Bisht

# LOD first and last value listing and comparison

Hello,

I have a dataset that includes the following fields: [Patient ID],  [Obs Date] and [Obs Value].  I need to create a dashboard that identifies the first and last dates per [Patient ID], list them along with associated [Obs Values] and finally compares the values to show if the results increased or decreased. Sheet 2 shows the raw data grouped by [Patient ID]. The desired result for patient "aaaaa" would show first and last  [Obs Dates] of 1/1/17 and 6/5/17 with associated results 22 and 15 (decreased).  Using the following LOD calculated fields I was able to calculate and list the first and last dates nicely on Sheet 4. Unfortunately I was not able to get the associated LastValue calculation to show the correct value.   It appears to be listing the last overall value. I attempted to use a LOD calculation but I kept getting formula errors.  Can you point me in the right direction. Thanks, Hector

FirstDate              ATTR({include [Patient ID]: MIN([Obs Date])} )

LastDate              ATTR({include [Patient ID]: max([Obs Date])} )

LastValue            IIF(ATTR([Obs Date])=[LastDate],ATTR([Obs Val]),null,0)

• ###### 1. Re: LOD first and last value listing and comparison

Hi Hector,

Below is my DS

STEP 1 -First Date Last Date as u did so not repeating

STEP 2-

Fixed Patient Max Date Value=

if [Obs Date]= {FIXED  [Patient ID]: MAX([Obs Date])} then [Obs Value] END

I can get the values 1 and 0 for Patient aaaa and bbbb (for latest date ) by dragging above calculation to rows

Please find attachment as well (10.3 Version)

Thanks,

Ritesh

• ###### 2. Re: LOD first and last value listing and comparison

Wow it worked. You are amazing!

I then created calculated fields [Dif] and [Change] and added to the dashboard yielding the following result.

[Dif]:          {FIXED [Patient ID]: sum([FixedPtMaxDateValue])} - {FIXED [Patient ID]: sum([FixedPtMinDateValue])}

The weird thing is that when I created the [Change] formula and added to the dashboard it combined the [Patiend ID and [FirstDate] columns. I tried the following different versions of the [Change] formula which game me the same results.  Otherwise the report looks correct.

Hector

[Change]:  IF  [Dif] > 0 then "Improved" ELSEIF  [Dif] < 0 then "Worsened" else "No Change" end

[Change]:  IF  {FIXED [Patient ID]: sum([Dif])} > 0 then "Improved"  ELSEIF  {FIXED [Patient ID]: SUM([Dif])} < 0 then "Worsened" else "No Change" end

• ###### 3. Re: LOD first and last value listing and comparison

You are almost there, you have limited number of rows/columns set by default  , just follow below

You might see 6 there , just change that to 16 and it will WORK

Please mark it as correct if it has helped you so that others having similar questions can benefit.

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 4. Re: LOD first and last value listing and comparison

Thank you so much. This is exactly what I needed! Hector

• ###### 5. Re: LOD first and last value listing and comparison

I am sorry but I am new to this blog and I want to be sure I follow all the processes. Where would I go to mark it as correct. I only see "Like", "Helped", "Actions" Hector

• ###### 6. Re: LOD first and last value listing and comparison

I think you are able to do it, thanks a lot