7 Replies Latest reply on May 27, 2015 2:58 PM by alejandro.molina

# max date OK, corresponding \$ not OK

I've simplified this for the purposes of this exercise. I have a table like so:

Wrap #Day of DateCustomer Name\$
W13102010/25/2013Customer A\$90
W1310206/19/2014Customer A\$1,100
W1310209/8/2014Customer A\$902
W1310209/26/2014Customer A\$903

#### \$2,100

Max(date) is a calculated field, formula max(date) - pretty straight forward.  But when I make my analysis, instead of getting the last two rows of the table (above table) which represent the last date each customer bought something, I get the right dates, but with the min \$ amount (or max \$) of what the customer bought, not what they bought that day. My results are:

 W131020 11/3/2014 Customer A \$90 W131021 10/8/2013 Customer B \$2,100

The dates are right, but it's not returning the \$ for that particular date. How do I grab not only the max / last day and also the \$ for that date only?

• ###### 1. Re: max date OK, corresponding \$ not OK

Is it the last date for each customer you want ? Regardless of the amount ? Or is it the last date having the max \$\$ ?

Thanks

• ###### 2. Re: max date OK, corresponding \$ not OK

Sophie:

To get what you are looking for, you have to use couple table calculations (window and index) and hope the attached helps.

..kk

• ###### 3. Re: max date OK, corresponding \$ not OK

Hi Karunaker, thank you for your input and sample workbook. However, I was already getting those results, but that is not what i wanted. I need the very row (based on date) for each Customer.  So I need this result:

#### \$2,100

• ###### 4. Re: max date OK, corresponding \$ not OK

Hi Cyril,  I do indeed need the last date, and the corresponding \$ amount.  In other words, I do not want a min or max \$ amount, I want the exact amount on the date it occurred.

In my case, for Customer A, I'd expect to see

#### \$903

and for Customer B,

#### \$2,100

Can't get my mind wrapped around how to get the right \$ amount for the last day of each transaction.

• ###### 5. Re: max date OK, corresponding \$ not OK

Sophie:

Ok. here you go. For this you need to look for the Date = window_max date for the customer

Refer to Sheet 3 of the attached.

Hope this helps

..kk

• ###### 6. Re: max date OK, corresponding \$ not OK

You just need to start with a rank. Create a field named Rank with Index() as formula and add it by a double click on it.

Then you need to adjust the way the index is computed. To do so you have to edit the table calculation of the Rank you just added. You choose advanced for compute, you add customer and then day of date. At the bottom you choose the day of date field, maximum as agregation and descending as order.

Last setting is to choose to restart the rank for every customer.

Then you can use your Rank as a filter to do whatever you want. Here you want the rank 1. But the could show the quick filter to have more.

This solution offers the maximum flexibility and help you understand how Index() can be used and computed.

1 of 1 people found this helpful
• ###### 7. Re: max date OK, corresponding \$ not OK

Hello KK Mologu .

I am very interested in you solution.

Following the example, imagine that Customer A and Customer B, belong to the Region East.

I am interested to know what the total Sum(\$) is, for the latest dates. In this case I expect the result of \$3,003 (903 + 2100)

I don't know how to configure the fields or calculation to obtain that result.