5 Replies Latest reply on Sep 25, 2018 7:57 AM by krishna.amilineni.0

# Get values for the latest date

Hi All,

I am in a situation, where I need to get the value of the variable for the latest date available in the data.

This is the sample data I have created to explain my case.

I want to put employees into different buckets(high/medium) according to their weights.

I get the counts of employees under each bucket. Here the same person is being counted twice.

I have tried using below in two separate  calculated fields

1. to get the maximum date

{ FIXED [MPI]: Max([Date])}

2. to get the weight for that date.

IF  [Date] = [Date(Max)] THEN   [weight] ELSE  '' END

But when I use this, the same person is counted twice in the viz but I want him/her to be considered only once but with the latest weight. which is 150.

Any help is appreciated.

Thanks

Krishna

• ###### 1. Re: Get values for the latest date

The FIXED calc is the right thing to do.

Keep in mind that the MAX calc will be on all rows for that [MPI].

If you put MPI on your sheet, and then do SUM(your second calc), are you getting a double count?  I guess it depends on how you are using the values, order of pills on your rows and columns, etc.

Can we see a sample workbook?

• ###### 2. Re: Get values for the latest date

Hi Krishna

You should use This

IF {FIXED EmpID:MAX(Date)}=Date Then Weight End

Thanks

Deepak

• ###### 4. Re: Get values for the latest date

Joe,

Thank you.

Yes,  the same person counted twice. Deepak's formula helped me.

• ###### 5. Re: Get values for the latest date

Thank you Deepak.

It helped me.  I have added else 0 to the formula and excluded 0 in the sheet. for my data.

And it worked.