Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Awesome
I am glad we were able to help you as a team here.
Best,
Sunny
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))
Thanks Sunny for coming to help.
As always, an awesome solution.
Thanks again.
No problem Sinan