Count repeated record

[#1]

Hi,

I wish to have a column that show the no. of repeated record :

I tried various COUNT & SUM filtering but failed. Appreciate if anyone could help.

Thank you.

Original table :

Expected result :

OMSPO#       trackCode    StatusDate(First)    Count

00O30868     REVPO         02/03/2015                  2

00O55871     REVPO        16/01/2015                   3

00O35872     REVPO         16/01/2015                  1

[dominicmander]

if you have OSMSPO# and trackCode as dimensions and then FirstSortedValue(StatusDate) and Count(StatusDate) as your two measures that should get you to that answer.

[dominicmander]

...sorry ... firstsortedvalue won't work in this context because you need something to sort by.

max(StatusDate) or min(StatusDate) would give you the earliest or latest date against each OMSOP#.

If you truly want just the first date then you would have to add a field to your load with "RowNo() as RowNumber" and then use firstsortedvalue(StatusDate,RowNumber)

[Anonymous · Accepted solution]

See if the attachment serves you.