Finding and counting the MAX date from a series of Dates based on a selection

[#1]

I am trying to count the number of stock movements based on a date as the dimension. But I am needing to only count the last occurance when a particular item have been moved more than once. In the example below:

Date       SM_Primary       Serial Number

Jan/12    1                        ABC

Jan/12    2                        ABD

Jan/12    3                        ABE

Feb/12    4                       ABF

Feb/12    5                       ABC

Feb/12    6                       ABG

Mar/12    7                       ABH

Apr/12    8                       ABI

I am using the date as my dimension with the serial number being counted in the expression. This would be fine if I wanted to count SM_Primary 1 and 5 in both Jan and Feb but what I need is to count only the one in Feb.

If anyone can help I would really appreciate it.Thanks

[#2]

What you could do is in the script do a resident Load of this table and do Max(Date) group by Serial Number. This will hard code the max date for each number so that you do not need to do a formula for max date and deal with other dimensions

[#3]

I totally understand what you are saying. I omitted a vital bit of information. The data that is returned varys depending on a value selected elsewhere. So the max date can shift about so again looking at the data above the data available may only include the transactions up to the end of January in which case January would need to = 3 as the dates after that don't count.

[#4]

Hi,

I am not sure if I understood what you need exactly but I tried a solution according to my understanding.

Can you pls. check the attached file to see if it works?

Best regards,

[#5]

Hi,

I think, my previous solution was not correct.

It needed a small modification.

Pls. see the new version attached.

Best regards,

http://quickdevtips.blogspot.com/