Sum and FirstSortedvalue issue

[#1]

Hi all,

I have a chart that has one dimension called PID.

I have several expressions (5) in total that are of the form

Cost = Firstsortedvalue({<YearMonth = >} Cost, -StartDate)

Status = Firstsortedvalue({<YearMonth = >} Status, -StartDate)

I would only like to have one row for each PID and the firstsorted value gives me the latest row as expected.

I am trying to do a sum in two textboxes to display the costs for all "Expired" statuses one for all "Active" statuses and I am unable to get it to work.

I have attached my table with some comments to make it easier to understand.

Any ideas please?

Thanks

[sunny_talwar]

Awesome

I am glad we were able to help you as a team here.

Best,

Sunny

[sunny_talwar]

These expression also seem to work:

Expired:

=Sum({1}Aggr(FirstSortedValue({<PID={"=FirstSortedValue(Status,-StartDate) = 'Expired'"}>} Cost,-StartDate),PID))

Active:

=Sum({1}Aggr(FirstSortedValue({<PID={"=FirstSortedValue(Status,-StartDate) = 'Active'"}>} Cost,-StartDate),PID))

Unknown End Date:

=Sum({1}Aggr(FirstSortedValue({<PID={"=FirstSortedValue(Status,-StartDate) = 'Unknown End Date'"}>} Cost,-StartDate),PID))

[sinanozdemir]

Thanks Sunny for coming to help.

As always, an awesome solution.

Thanks again.

[sunny_talwar]

No problem Sinan